QUERY-TUNING phrase

Allows programmatic control over the execution of a query in a DataServer application. This phrase is available for the DataServers; it is not available for queries of OpenEdge databases.

Syntax 

 

QUERY-TUNING ( { [ ARRAY-MESSAGE | NO-ARRAY-MESSAGE ] [ BIND-WHERE | NO-BIND-WHERE ] [ CACHE-SIZE integer ] [ DEBUG { SQL | EXTENDED diag-option } | NO-DEBUG ] [ INDEX-HINT | NO-INDEX-HINT ] [ JOIN-BY-SQLDB | NO-JOIN-BY-SQLDB ] [ LOOKAHEAD | NO-LOOKAHEAD ] [ ORDERED-JOIN ] [ REVERSE-FROM ] [ SEPARATE-CONNECTION | NO-SEPARATE-CONNECTION ] } )

The following descriptions are general. For more detailed information, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server, OpenEdge Data Management: DataServer for ODBC, and OpenEdge Data Management: DataServer for Oracle).

ARRAY-MESSAGE | NO-ARRAY-MESSAGE

Specifies whether the DataServer sends multiple result rows in a single logical network message.

The default is ARRAY-MESSAGE.

BIND-WHERE | NO-BIND-WHERE

This option is available only for the DataServer for ORACLE.

Specifies whether the DataServer uses ORACLE bind variables or literals in WHERE clauses. If you use NO-BIND-WHERE, the DataServer uses literals. Bind variables can improve performance, but ORACLE produces some unexpected results for some data types.

The default is BIND-WHERE.

CACHE-SIZE integer[ROW|BYTE]

Specifies the maximum cache size the DataServer can use when fetching records for a lookahead or standard cursor. You can optionally specify the size of the cache information in either bytes or records. The following values are for ORACLE.

The default is 1024 for standard cursors and 8192 for lookahead cursors.

If you use the byte option, the byte maximum is 65535 bytes and the byte minimum specifies the number of bytes contained in a single record. For joins, you must specify the number of bytes contained in two records.

If you use the row option, the row maximum equals the maximum number of records that can be fit in 65535 bytes. The row minimum is 1 row for a single table and 1 rows for a join.

The default is 30000.

{ DEBUG { SQL|EXTENDED diag-option } } | NO-DEBUG

Specifies whether the DataServer should print debugging information for the query to the dataserv.lg file.

The SQL option prints the SQL executed by the DataServer against the non-OpenEdge DBMS. The extended option prints additional information, such as cursor statistics. The information you get when you use the EXTENDED option can be helpful in setting your parameters.

The default is NO-DEBUG.

EXTENDED diag-option

The syntax for the diagnostic options is as follows:

 

Syntax 

EXTENDED CURSOR | DATA-BIND | PERFORMANCE | VERBOSE

For more information, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server, OpenEdge Data Management: DataServer for ODBC, and OpenEdge Data Management: DataServer for Oracle).

HINT

This option is only available for the DataServer for ORACLE.

Specifies the ORACLE hint syntax that the DataServer passes directly to the ORACLE DBMS as part of the query. This allows you to control which hints are passed as opposed to the index hints that the DataServer passes when appropriate.

INDEX-HINT | NO-INDEX-HINT

This option is available only for the DataServer for ORACLE.

Specifies whether the DataServer provides index hints to the ORACLE DBMS. INDEX-HINT places index hints in the generated SQL; NOINDEX-HINT prevents the use of index hints.

The default is INDEX-HINT.

JOIN-BY-SQLDB | NO-JOIN-BY-SQLDB

Specifies whether the non-OpenEdge DBMS can perform joins when possible, which usually improves performance.

The default is JOIN-BY-SQLDB.

LOOKAHEAD | NO-LOOKAHEAD

Specifies whether the DataServer uses lookahead or standard cursors. Lookahead cursors fetch as many records as can fit into the allocated cache, which reduces the number of database accesses and improves performance.

The default is LOOKAHEAD, except with statements that use an EXCLUSIVE lock.

ORDERED-JOIN

Specifies that the DataServer embed the ORDERED hint syntax in the SQL it generates. Applies to ORACLE only.

REVERSE-FROM

Specifies that tables are joined in the reverse order in which they appear in the FROM clause. Applies to ORACLE only.

SEPARATE-CONNECTION | NO-SEPARATE-CONNECTION

Creates a new connection for each cursor that the DataServer opens. Applies to the OpenEdge DataServer for ODBC only.

Example 

The following code fragment illustrates a QUERY-TUNING phrase in a FOR EACH statement. In this example, the DataServer uses lookahead cursors with a cache size of 32K and records debugging information:

 

FOR EACH Customer, EACH Order OF Customer WHERE Order.OrdNum > 20 BY Customer.CustNum QUERY-TUNING(LOOKAHEAD CACHE-SIZE 32768 DEBUG EXTENDED) TRANSACTION:

Note 

For the DataServer for ORACLE, all options of the QUERY-TUNING phrase are effective at both compile and run time, except INDEX-HINT, NO-INDEX-HINT, JOIN-BY-SQLDB, and NO-JOIN-BY-SQLDB, which are only effective at compile time.

For more information on the QUERY-TUNING phrase, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server, OpenEdge Data Management: DataServer for ODBC, and OpenEdge Data Management: DataServer for Oracle).

See also 

DO statement, FOR statement, OPEN QUERY statement, REPEAT statement