Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## Writing queries for performance

> This section provides a collection of tips and guidelines to follow when writing queries. For example, a query that processes a large number of rows performs best if it uses `NO-LOCK`, lookahead cursors, a large cache size, and a small field list. The following suggestions might help improve the performance of your DataServer applications. Try some of the following and use the `DEBUG` diagnostic options to gather statistics on how your application runs:

> - Use `FOR` `EACH`, `GET`, and `OPEN` `QUERY` statements as opposed to `FIND` statements, which generally perform more slowly. Consider using the `FOR` `FIRST` statement instead of `FIND` `FIRST`. The only exception is that `FIND` `LAST` is faster than `GET` `LAST` because `GET` `LAST` causes the client to process all the records. The `FIND` `LAST` statement allows the server to retrieve the last record.

> - Take advantage of field lists.

> - Take advantage of the `QUERY-TUNING` options.

> - Use lookahead cursors.

> - Use `NO-LOCK` where possible.

> - Avoid specifying lock upgrades. Allow the DataServer and ORACLE to handle lock upgrades.

> - Do not ask for a particular ordering of results with `USE-INDEX` or `BY` clauses, unless your application requires it. Allow the DataServer and ORACLE to determine which index (if any) is most efficient for processing a query and avoid the overhead of sorting results.

> - For aggregates, use the `RUN-STORED-PROC` `send-sql-statement` syntax or use a Progress SQL statement. If you use a Progress SQL statement with a cursor, declare the cursor read-only.

> - If you are testing for the existence of a record, use the `CAN-FIND` function, which does not retrieve the record if the DataServer passes the entire `WHERE` clause to ORACLE for processing. However, avoid nesting `CAN-FIND` functions.

> - Avoid using the `RECID` function. Use the `ROWID` function.

> See the "Analyzing performance" section for information on collecting statistics.

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