Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Additional Features to Enhance DataServer Performance : Query tuning
 

Query tuning

How you structure a query determines how efficiently you access a database. Using your MS SQL Server data source efficiently enhances the performance of DataServer applications. The standard approach to enhancement is using selection criteria to refine access to data, but you can further optimize the DataServer's execution of a query by specifying the OpenEdge QUERY-TUNING phrase.
You can include the QUERY-TUNING phrase in these OpenEdge statements:
*FOR EACH
FOR EACH table QUERY-TUNING(query-tuning-option
query-tuning-option...)
*OPEN QUERY
OPEN QUERY query QUERY-TUNING(query-tuning-option
query-tuning-option...)
*DO PRESELECT
DO PRESELECT table QUERY-TUNING(query-tuning-option
query-tuning-option...)
*REPEAT PRESELECT
REPEAT PRESELECT table QUERY-TUNING(query-tuning-option
query-tuning-option...)
You must place the QUERY-TUNING phrase after the last record phrase. For example, place it near the end of the statement where you also place block modifier phrases such as BREAK, ON ERROR, and TRANSACTION.
You can include multiple query-tuning options in a single statement; simply separate each option from the previous one by a single space.
The following table describes the query-tuning options.
Table 24. Query-tuning options
Option
Description
ARRAY-MESSAGE
NO-ARRAY-MESSAGE
Specifies whether the DataServer sends multiple result rows in a single logical network message, thereby reducing network traffic.
Default: ARRAY-MESSAGE, if the query uses a lookahead cursor.
CACHE-SIZE integer
Specifies the size in bytes of the cache used by lookahead cursors. A larger cache size can improve performance for queries that return a large number of records because the DataServer might need fewer SQL statements to get the results. This value will override a cache size specified with -Dsrv QT_CACHE_SIZE.
Minimum: The DataServer always caches at least one record.
Maximum: None.
Default: 30000.
DEBUG EXTENDED
DEBUG SQL
NO-DEBUG
Specifies whether the DataServer should print to the dataserv.lg file the debugging information that it generates for a query.
Specify DEBUG SQL to print only the SQL that the DataServer executes against the ODBC data source.
Specify DEBUG EXTENDED to print additional information, such as cursor statistics.
Specify DEBUGoption to override the NO-DEBUG default.
Default: NO-DEBUG.
JOIN-BY-SQLDB
NO-JOIN-BY-SQLDB
Specifies whether the DataServer allows its data source to perform a join (this usually improves performance). JOIN-BY-SQLDB implies SEPARATE-CONNECTION queries that include joins. For more information on JOIN-BY-SQLDB, see Join by SQLDB.
Default: JOIN-BY-SQLDB
JOIN-BY-SQLDB is a compile-time option. A query must be compiled to use or not use this option.
You can turn off the JOIN-BY-SQLDB default globally at compile time by specifying the Server Join (-nojoinbysqldb) startup parameter when you start an OpenEdge session. This parameter does not override the explicit use of JOIN-BY-SQLDB in the QUERY-TUNING phrase.
Note: JOIN-BY-SQLDB is the default behavior for outer join operations.
LOOKAHEAD
NO-LOOKAHEAD
Specifies whether the DataServer uses lookahead or standard cursors. Lookahead cursors fetch as many records as fit in the allocated cache (see the CACHE-SIZE entry in this table). This reduces the number of SQL statements and network messages that are required, thereby improving performance.
Using lookahead cursors results in behavior that is different from an OpenEdge database because changes made to the records in the cache might not be immediately visible. Specify NO-LOOKAHEAD for behavior that is consistent with OpenEdge.
Default: LOOKAHEAD, when statements use NO-LOCK or when statements use SHARE-LOCK with TXN_ISOLATION level set to 1 (read uncommitted.)
SEPARATE-CONNECTION
NO-SEPARATE-CONNECTION
Specifies whether each cursor should use a separate database connection. Executing cursors in separate connections might improve performance because the DataServer does not have to restart the cursors and sort the results.
Do not specify SEPARATE-CONNECTION if you require behavior that is consistent with OpenEdge.
Default: NO-SEPARATE-CONNECTION except in certain cases. For details, see Managing connections to an MS SQL Server database.
NO-QUERY-ORDER-ADDED
Specifies that OpenEdge should not choose an index in the absence of a USE-INDEX or BY clause in the query request. OpenEdge may otherwise select an index if it is needed to provide ABL language compatibility.
Note: If you elect to use this option to omit index selection on the query, you may see better performance using the optimizer's sort selections. However, compatibility with OpenEdge forward/backward scrolling and reposition capability may be lost. Only use this option when compatibility is not required and can be overlooked for the sake of better performance.
NO-QUERY-UNIQUE-ADDED
Specifies that OpenEdge should omit the record identifier from the end of the query's generated ORDER BY clause when trying to obtain record uniqueness from a selected non-unique index. A sort order that is modified to derive uniqueness may produce a query that can't find a useful index to perform sorting thus impacting query performance.
Note: If you elect to use this option, the query may find an index match to provide better performance. However, turning off uniqueness in a query where scrolling is required may result in behavior that is incompatible with the OpenEdge ABL. Only use this option when compatibility is not required and can be overlooked for the sake of better performance.
FIREHOSE-CURSOR
NO-FIREHOSE-CURSOR
Specifies at the query level that the firehose cursor type should be considered to satisfy the query when the NO-LOCK phrase is used.
Note: This query-level option overrides the connection-level -Dsrv options, QT_FIREHOSE and QT_NO_FIREHOSE that determine if firehose cursors should be considered for the DataServer connection.
Specifies at the query level that the firehose cursor type should not be considered to satisfy the query when the NO-LOCK phrase is used.
Note: This query-level option overrides the connection-level -Dsrv options, QT_FIREHOSE and QT-NO-FIREHOSE that determine if firehose cursors should be considered for the DataServer connection.
By default, firehose cursors are available to satisfy NO-LOCK queries during a DataServer session. It is generally recommended this default use be retained and overridden by QT-NO-FIREHOSE on an individual query basis in the event that slow query performance is observed on a very large result set.
SELECT-ON-JOIN
JOIN-ON-SELECT
When performing an outer join operation, SELECT-ON-JOIN specifies that any search condition separate from the join predicate be bracketed after the join is performed. This option eliminates all the records with NULL values contained in the non-matching results, if the columns with the NULL values appear in the join search criteria.
When performing an outer join operation, JOIN-ON-SELECT specifies that any search condition separate from the join predicate be bracketed before the join is performed. This option includes all the records with NULL values contained in the non-matching results and causes records that join on NULL to be included in the results of an OUTER-JOIN which is consistent with OpenEdge join handling of the unknown value.
Default: JOIN-ON-SELECT.
All but two of the QUERY-TUNING options take effect at both compile time and run time. The exceptions are JOIN-BY-SQLDB and NO-JOIN-BY-SQLDB, which apply only at compile time. You can override query-tuning defaults (except JOIN-BY-SQLDB) at run-time by specifying the appropriate startup parameters.
The following example shows how to use the QUERY-TUNING phrase to enhance performance. It includes a join, JOIN-BY-SQLDB, that the DataServer instructs the MS SQL Server data source to perform by default, as shown:
FOR EACH customer, EACH order OF customer WHERE order.ordernum GT 20
  BY customer.custnum QUERY-TUNING (JOIN-BY-SQLDB)
The QUERY-TUNING options in this example specifies the following:
*Lookahead cursors are not used (the NO-LOOKAHEAD option)
*The DataServer writes an extended report on the SQL statements that it executes (the DEBUG EXTENDED option)
When the DataServer constructs queries for a MS SQL Server data source, it uses the QUERY-TUNING options that you specify as guidelines. This is because there might be syntax considerations that prevent the DataServer from applying the QUERY-TUNING options as specified. In such a case, the DataServer executes the query using the most appropriate options.
Note: The DataServer does not issue errors or warnings if it does not apply the QUERY-TUNING options that you specify.