Progress
DataServer
for ODBC Guide


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 Progress QUERY–TUNING phrase.

You can include the QUERY–TUNING phrase in these Progress statements:

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.

Table 2–11 describes the query-tuning options.

Table 2–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: 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 DEBUG option to override the NO–DEBUG default.
In addition to EXTENDED and SQL, there are other options that can assist you in analyzing performance.
Default: NO–DEBUG
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 Progress 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" in Connecting the DataServer."

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 that the DataServer instructs the ODBC data source to perform by default:

FOR EACH customer, EACH order OF customer WHERE ord-num > 20
		BY cust-num
		QUERY-TUNING(NO-LOOKAHEAD DEBUG EXTENDED)
		TRANSACTION: 

The QUERY–TUNING options in this example specify the following:

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.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095