skip to main content
OpenEdge Data Management: DataServer for ODBC
Programming Considerations : Enhancing DataServer performance : Query tuning
 

Query tuning

How you structure a query determines how efficiently you access a database. Using your ODBC 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 ABL QUERY–TUNING phrase.
You can include the QUERY–TUNING phrase in these ABL statements:
*FOR EACH
Syntax
FOR EACH table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
*OPEN QUERY
Syntax
OPEN QUERY query QUERY-TUNING(query-tuning-optionquery-tuning-option...)
*DO PRESELECT
Syntax
DO PRESELECT table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
*REPEAT PRESELECT
Syntax
REPEAT PRESELECT table QUERY-TUNING(query-tuning-optionquery-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 11. 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.Minimum: The DataServer always caches at least one record.Maximum: NoneDefault: 30000
DEBUG SQL
DEBUG EXTENDED
NO–DEBUG
Specifies whether the DataServer should print to the dataserv.lg file the debugging information that it generates for a query.Default: NO–DEBUGSpecify DEBUG SQL to print the SQL that the DataServer executes against the ODBC data source.Specify DEBUG EXTENDED to print the generated SQL plus additional information, such as cursor statistics.
Note: In addition to EXTENDED and SQL, there are other options that can assist you in analyzing performance.
JOIN–BY–SQLDB
NO–JOIN–BY–SQLDB
Specifies whether the DataServer allows an ODBC data source to perform a join (this usually improves performance). JOIN–BY–SQLDB implies SEPARATE–CONNECTION queries that include joins.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 a client session. This parameter does not override the explicit use of JOIN–BY–SQLDB in the QUERY–TUNING phrase.
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 Progress because changes made to the records in the cache might not be immediately visible. Specify NO–LOOKAHEAD for behavior that is consistent with Progress.Default: LOOKAHEAD when statements use NO–LOCK or SHARE–LOCK with transaction isolation level equal to 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 Progress.Default: NO–SEPARATE–CONNECTION except in certain cases. For details, see Managing connections to an ODBC data source.
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 for better performance.
All but two of the QUERY–TUNING options take effect at both compile time and runtime. 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, including a join that the DataServer instructs the ODBC data source to perform by default:
FOR EACH customer, EACH order OF customer WHERE order.ordnum > 20
 BY customer.custnum QUERY-TUNING(NO-LOOKAHEAD DEBUG EXTENDED) TRANSACTION:
The QUERY–TUNING options in this example specify 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 an ODBC 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.