DQL Statements › Performance Considerations

Performance Considerations

Using a COUNT statement rather than a FIND statement can significantly improve the performance of your query in its search for the specified rows. Because no data is retrieved, the search requires fewer resources. In some cases, the difference in performance will be small.

Using keys in the selection criteria for your WITH clause can significantly improve the performance of your FIND or COUNT statement in its search for the specified rows. The purpose of keys is to expedite the search for data. In some cases, specifying a column in your selection criteria can be almost as efficient as using a key. Using an arithmetic expression in your selection criteria will nearly always require more overhead than specifying a key or column in the WITH clause.

When you specify relational criteria in a relationship clause, again the use of keys can be more efficient than specifying a column or a literal value to join the tables your query searches.

Your choice of operators in the WITH clause can also affect the efficiency of the FIND. For example, using a key and EQUAL as the operator for your selection criteria can be more efficient than choosing the combination of masking and another operator, such as GREATER THAN. See WITH Clauses for details about operators.

When using a left join or left disjoin, as discussed on Outer Joins, performance should not be negatively impacted. Performing a right join or right disjoin could be costly for large tables. These joins are not reversible.

In the case of the join of three or more tables, the result of the processing would be as if the joins were done two at a time in the order written, with the result of the join of the first pair of tables being joined to the third table, and so on.

Getting Assistance

If you have concerns about the performance of your selection criteria and relationship criteria in a query, your Database Administrator or CA Dataquery Administrator can help you determine which combination of keys or columns and operators will make your query most efficient.