FOR EACH table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
|
OPEN QUERY query QUERY-TUNING(query-tuning-optionquery-tuning-option...)
|
DO PRESELECT table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
|
REPEAT PRESELECT table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
|
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.
|
FOR EACH customer, EACH order OF customer WHERE order.ordnum > 20
BY customer.custnum QUERY-TUNING(NO-LOOKAHEAD DEBUG EXTENDED) TRANSACTION: |