Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

> ## 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.

> These tips and guidelines might improve the performance of your DataServer applications. To assess the usefulness of a particular suggestion, apply it, then use the `DEBUG` diagnostic options to gather statistics on how your application runs. (See the "Analyzing performance" section for information on collecting statistics.)

> - Use `FOR` `EACH`, `GET`, and `OPEN QUERY` statements rather than `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`. This is because `GET` `LAST` causes the client to process all of the records; the `FIND` `LAST` statement allows the server to retrieve the last record.

> - Use field lists.

> - Use the `QUERY–TUNING` options.

> - Use lookahead cursors.

> - Use `NO–LOCK` where possible.

> - Avoid specifying lock upgrades. Instead, allow the DataServer and the ODBC data source to handle lock upgrades.

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

> - If you use a `BY` clause that will sort a large amount of data, make sure a corresponding index exists in your data source to make sorting efficient. In some cases it may also be desirable to have indexes over columns used in `WHERE` clause selection criteria.

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

> - Avoid using the `RECID` function. Instead, use the `ROWID` function.

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