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:
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 FINDFIRST.
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 MS SQL Server 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 MS SQL Server 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 an OpenEdge SQL statement. If you use an OpenEdge SQL statement with a cursor, declare the cursor read-only.
When you test for the existence of a record, use the CAN-FIND FIRST function, which does not retrieve the record if the DataServer passes the entire WHERE clause to the MS SQL Server data source for processing. However, avoid nesting CAN-FIND functions.
Avoid using the RECID function. Instead, use the ROWID function.
Avoid queries that include server LOB types in their results if the LOB data is not pertinent with the needs of your application. LOB types, and their processing overhead, can be omitted from your query results using field lists.
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.