Try OpenEdge Now
skip to main content
DataServer for Oracle
Additional Features to Enhance DataServer Performance : Query tuning
 

Query tuning

In addition to the standard approach of using selection criteria to refine access to data, you can further influence how the DataServer executes a query through the ABL QUERY-TUNING phrase. How you structure a query determines how efficiently you access a database. Efficient use of the Oracle RDBMS enhances the performance of DataServer applications. You can include the QUERY-TUNING phrase for the following ABL statements:
*FOR EACH
FOR EACH table QUERY-TUNING
( query-tuning-optionquery-tuning-option... )
*OPEN QUERY
OPEN QUERY query QUERY-TUNING
( query-tuning-optionquery-tuning-option... )
*DO PRESELECT
DO PRESELECT table QUERY-TUNING
( query-tuning-optionquery-tuning-option... )
*REPEAT PRESELECT
REPEAT PRESELECT table QUERY-TUNING
( query-tuning-optionquery-tuning-option... )
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. Separate multiple query-tuning options by a single space. The QUERY-TUNING options have equivalent startup parameters. You cannot use the startup parameters to override the QUERY-TUNING settings.
The following table describes the query-tuning options.
Table 23. 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.
BIND-WHERE NO-BIND-WHERE
Specifies whether the DataServer uses Oracle bind variables for values in WHERE clauses. Using bind variables typically improves performance, but Oracle provides unexpected results for some operations, such as a MATCHES on an indexed field and a trailing wild card or comparisons of CHAR fields that use Oracle's blank-padding rules.
Specify NO-BIND-WHERE to use literals. Using NO-BIND-WHERE in queries that do comparisons (MATCHES, BEGINS on an indexed field) can improve performance.
Default: BIND-WHERE.
CACHE-SIZE integer BYTE CACHE-SIZE integer ROW
Specifies the size of the cache for information (in bytes or records) used by lookahead or standard cursors. If you have two ABL statements that cause the DataServer to generate identical SQL code except that the second statement specifies a smaller cache size, the DataServer reuses the larger cache from the first statement if the cursor is still available. Reusing cache and cursors improves performance.
Byte maximum: 65535 bytes.
Byte minimum: Specify the number of bytes contained in a single record. For joins, specify the number of bytes contained in two joined records. By default, the DataServer sizes the cache to accommodate one record or, for a join, two joined records. For example, if a join returns a 500-byte record, you need a cache of at least 1000 bytes.
Default: 1024 bytes with standard cursors; 8192 with lookahead cursors.
Row maximum: the number of records that can fit in 65535 bytes. See Caching records for more information.Row minimum: 1 for a single table; 2 for a join.
DEBUG EXTENDED DEBUG SQL NO-DEBUG
Specifies whether the DataServer should print debugging information that it generates for the query to the dataserv.lg file.
Specify DEBUG SQL to print the SQL that the DataServer executes against the Oracle DBMS.
Specify DEBUG EXTENDED to print additional information, such as cursor statistics.
There are additional options for collecting advanced statistics with DEBUG. See Analyzing application execution with Enhanced Logger for more information.
Default: NO-DEBUG.
HINT string1 string2 string3
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.
When you have to specify an index name in the hint syntax, use the name defined in the Oracle database. Because the DataServer generates aliases for Oracle tables using names from T0 through T9, use these aliases to refer to tables in the hint syntax.
The DataServer passes the opening symbols (/*+) and closing symbols (*/). For example, to pass the /*+ORDERED*/ hint syntax, you specify only HINT "ORDERED".
Passing incorrect hint syntax, inappropriate hints, or conflicting hints will not return an error but might give you unpredictable results. See your Oracle documentation for information on hint syntax.
INDEX-HINT NO-INDEX-HINT
Specifies whether the DataServer should provide index hints to the Oracle DBMS. Generally, index hints improve performance, but Oracle's responses to hints vary between releases.
Specify NO-INDEX-HINT to test whether performance for a query improves when the DataServer executes it without hints. See Indexes for more information on index hints.
By default, the DataServer passes index hints. You can turn off the default globally at compile time or run time by specifying the -noindexhint startup parameter when you start an OpenEdge session. Use INDEX-HINT to retain the behavior for individual queries.
Default: INDEX-HINT.
JOIN-BY-SQLDB NO-JOIN-BY-SQLDB
Specifies whether the DataServer allows the Oracle DBMS to perform a join, which usually improves performance.
Default: JOIN-BY-SQLDB.You can turn off the JOIN-BY-SQLDB default globally at compile time by specifying the -nojoinbysqldb startup parameter when you start an OpenEdge session. The -nojoinbysqldb parameter does not override the explicit use of JOIN-BY-SQLDB in the QUERY-TUNING phrase.
Note: JOIN-BY-SQLDB is the default behavior for all outer join operations.
LOOKAHEAD NO-LOOKAHEAD
Specifies whether the DataServer uses lookahead or standard cursors. Lookahead cursors fetch as many records as fit in the allocated cache (CACHE-SIZE), which limits the number of database accesses, thereby improving performance.
Using lookahead cursors results in behavior that is different from ABL because the client does not see any changes made to the records in the cache. Specify NO-LOOKAHEAD for behavior that is consistent with ABL.
Default: LOOKAHEAD, except with FIND statements and statements that use an EXCLUSIVE lock.
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 might see better performance using the optimizer's sort selections. However, compatibility with OpenEdge forward/backward scrolling and reposition capability might 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, or a join query's join crieteria, 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.
ORDERED-JOIN
Specifies that the DataServer embed the ORDERED hint syntax in the SQL it generates.
REVERSE-FROM
Specifies that Oracle join tables in the reverse order in which they appear in the FROM clause. The DataServer generates a new SQL FROM clause with the tables in reverse order.
REVERSE-FROM is independent of the ORDERED-JOIN option.
SELECT-ON-JOIN
JOIN-ON-SELECT
When performing an outer join operation, SELECT-ON-JOIN specifies that any search condition separate from the join predicate be bracketed after the join is performed. This option eliminates all the records with NULL values contained in the non-matching results, if the columns with the NULL values appear in the join search criteria.
When performing an outer join operation, JOIN-ON-SELECT specifies that any search condition separate from the join predicate be bracketed before the join is performed. This option includes all the records with NULL values contained in the non-matching results and causes records that join on NULL to be included in the results of an OUTER-JOIN which is consistent with OpenEdge join handling of the unknown value.
Default: JOIN-ON-SELECT.
Note: All of the query-tuning options take effect at both compile and runtime except for the INDEX-HINT, JOIN-BY-SQLDB, and NO-JOIN-BY-SQLDB options, which apply only at compile time.
The following example shows how to use the QUERY-TUNING phrase to enhance performance. It includes a join which the DataServer instructs Oracle to perform by default. The QUERY-TUNING options specify that no lookahead cursors will be used. In addition, the DataServer will write an extended report on the SQL statements it executes, as shown:
FOR EACH customer, EACH order OF customer WHERE order.ordernum > 20
  BY custnum QUERY-TUNING(NO-LOOKAHEAD DEBUG EXTENDED) TRANSACTION:
This example shows how to use the QUERY-TUNING phrase to manage cache size so that the DataServer can reuse cursors and cache, thereby improving performance. The phrase also passes a hint to the Oracle optimizer to choose the cost-based approach to optimize the statement for best response time. Finally, the DEBUG EXTENDED option causes the DataServer to report on the SQL statements it executes, as shown:
FOR EACH customer, EACH order OF customer WHERE order.ordernum > 20
  QUERY-TUNING(CACHE-SIZE 20 ROW HINT "FIRST_ROWS" DEBUG EXTENDED)   TRANSACTION: