Progress
DataServer
for ODBC Guide


Query Tuning with Connection and Startup Parameters

You can control aspects of DataServer query handling not only programmatically within 4GL statements, but also through startup and connection parameter options as described in this section.

NOTE: Startup and connection parameters override query-tuning defaults. However, options set in the QUERY–TUNING phrase take precedence over startup and connection parameters. For example, if you specify NO–DEBUG for a query within your application, specifying qt_debug,SQL at connection time overrides the default application behavior but does not override the NO–DEBUG option that you specified for the query. See the "Query Tuning" section in "Programming Considerations," for more information.

You override query-tuning defaults with the DataServer (-Dsrv) connection parameter when you connect to an ODBC data source. This is the syntax:

SYNTAX
CONNECT data-source-name -dt ODBC
  -Dsrv query-tuning-option1,value1
  -Dsrv query-tuning-option2,value2. 

An alternate syntax for the -Dsrv parameter follows:

SYNTAX
CONNECT data-source-name -dt ODBC -Dsrv
  query-tuning-option1,value1,query-tuning-option2,value2. 

Table 4–2 describes the query-tuning options that you can specify with the -Dsrv parameter.

Table 4–2: Connection Query-tuning Options
Option
Description
qt_no_debug
qt_debug,SQL
qt_debug,EXTENDED
qt_debug,CURSOR
qt_debug,PERFORMANCE
qt_debug,CALL_SUMMARY
qt_debug,VERBOSE
Specifies whether the DataServer prints debugging information that it generates for the query to the dataserv.lg file. The default is qt_no_debug, to supply no debugging information. To override the default, specify qt_debug,option as follows:
    • Specify qt_debug,SQL to print the SQL statements that the DataServer executes against the ODBC data source.
    • Specify qt_debug,EXTENDED to print information such as cursor statistics in addition to the SQL statements executed by the DataServer.
    • Specify qt_debug,CURSOR to print information about the cursors that the DataServer uses for internal calls and for opening queries.
    • Specify qt_debug,PERFORMANCE to print information on the amount of time that certain operations take.
    • Specify qt_debug,CALL_SUMMARY to print information on cursors and timing.
    • Specify qt_debug,VERBOSE to print all of the information gathered by the other qt_debug options.
For more detailed descriptions of these options, see Table 4–3.
qt_lookahead
qt_no_lookahead
Specifies whether the DataServer uses lookahead or standard cursors. Lookahead is the default when you use NO–LOCK in a 4GL statement. When isolation level is set to read-uncommitted, lookahead is also the default whenever the 4GL does not specify EXCLUSIVE–LOCK.
Specify qt_no_lookahead for behavior that is consistent with Progress.
qt_separate_connection
qt_no_separate_connection
Specifies whether each cursor should use a separate connection to the ODBC data source. The default is qt_no_separate_connection, which provides behavior that is consistent with Progress.
Specify qt_separate_connection to use a separate connection. Executing cursors in separate connections can improve performance because the DataServer does not have to restart the cursors.
qt_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

The following example shows how to use the query-tuning options to enhance performance. The DataServer opens a separate connection to ODBC for each cursor and writes an extended report on the SQL statements it executes:

CONNECT holder -db infdb -dt ODBC -ld demo -U user -P password -Dsrv 
qt_separate_connection,qt_debug,EXTENDED. 

Progress provides a startup parameter called Server Join (-nojoinbysqldb) that controls the default JOIN–BY–SQLDB behavior. You specify this parameter in the startup command for your Progress session. It overrides the JOIN–BY–SQLDB default so that the client evaluates and performs joins. Using this parameter might slow performance, but it provides results that are consistent with Progress behavior. It also allows you to run DataServer applications on Version 8 clients with Version 7 DataServers. See "Programming Considerations," for more information.


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