ENFORM User's Guide

Improving Performance
Using ENFORM Efficiently
058058 Tandem Computers Incorporated 5–13
Some changes to query wording that might reduce physical file accesses are:
Reduce the number of records selected by adding request qualifications (a
WHERE clause) to the query.
Experiment by changing the way field names are qualified in a WHERE clause.
Determine if queries can share FIND files.
Add a WHERE Clause
Take advantage of information that is known to you but not known to the query
processor and add a WHERE clause to your query. Adding a WHERE clause might
force the query processor to search the data base in a more efficient manner by
reducing the number of logical reads needed. Use the ENFORM statistics described
earlier in this section to see if you are reducing the number of logical reads.
An understanding of the method that the query processor uses to examine the logical
expression of a WHERE clause is useful. When the query processor determines its
search strategy, it looks at the compiled query representation for the clause that most
restricts the records in a particular file. The query processor searches the clauses
looking for a logical expression where a primary key is equal to a constant. Failing to
find such an expression, the query processor looks for a logical expression where an
alternate key is equal to a constant. If neither exists, the query processor looks for a
logical expression where a primary key is compared to a constant by a conditional
operator such as less than or greater than. Finally the query processor looks for a
logical expression where an alternate key is compared to a constant by a conditional
operator.
For example, to force the query processor to search the region records first without
restricting the number of target records returned, add a request qualification such as:
WHERE region.regnum GT 0;
When comparing a key to a constant, avoid specifying the key in an arithmetic
expression such as:
WHERE key - 1 = x
Instead, specify the key alone on one side of the conditional operator and perform any
arithmetic operations on the field or constant to which the key is compared:
WHERE key = x + 1
Change the Qualification of Field Names in a WHERE Clause
Experiment with the way you qualify a field name in a WHERE clause if that same
field has been specified in a LINK statement. When a linking field name is specified in
a WHERE clause in the same query, qualify the field name with the record name that
corresponds to the smallest number of records. The result remains the same no matter
which record name is used to qualify the field name; however, qualifying a field name
with the record name associated with the fewest records often results in a more
efficient search strategy.