Optimizing Record Selection and Projection

Regardless of the OPTIMIZATION setting, the data adapter may pass record selection and projection to the RDBMS; it is always more efficient to do so.

In this section: Record Selection Projection

Record Selection

The data adapter can translate all forms of FOCUS record selection to predicates of an SQL WHERE clause, except those that contain:

• Certain DEFINE fields (see Optimizing DEFINE Fields).
• EDIT to perform data type conversions, EDIT of an alphanumeric field that was the result of such a conversion, or EDIT of a DEFINE field.
• LIKE with fields created using DEFINE or COMPUTE.
• DATE fields with formats other than YMD or YYMD.
• The only optimized selection criteria on date-time columns (criteria passed to the RDBMS in the generated SQL) are relational expressions that are valid in an IF phrase. For example, comparison of a date-time column with a date-time literal value is optimized, but comparison of a date-time column with another date-time column is not optimized.

The data adapter optimizes screening conditions based on DEFINE fields that derive their values from a single segment of the join structure. The data adapter optimizes these screening conditions as long as you do not set OPTIMIZATION OFF, even if it has disabled join optimization in your request.

When using LIKE in a WHERE clause, make sure any constant in the LIKE predicate either:

• Has the same length as the field used in the comparison.
• Is padded with blanks or underscore characters (_) to maintain the appropriate length.
• Contains the % wildcard character to denote any sequence of characters.

Escape characters in the LIKE predicate are optimized. See your FOCUS documentation for a discussion of LIKE.

If you fail to specify a wildcard pattern in the LIKE predicate, the WHERE clause passes an equality predicate to the RDBMS instead of the LIKE predicate. For example

WHERE EID LIKE 'A'

will generate

WHERE (T1.EID = 'A')

not:

WHERE (T1.EID LIKE 'A')

In addition, because of unpredictable comparisons between VARCHAR data types, the WHERE clause is not passed to the RDBMS if both of the following conditions are true:

• The specified pattern is not equal in length to the column used in the comparison.
• The pattern contains one or more wildcard characters but does not terminate with the percent character (%).

In this case, FOCUS performs the screening condition on all rows returned from the RDBMS.

Projection

In relational terminology, projection is the act of retrieving any subset of the available columns from a table. The data adapter implements projection by retrieving only those columns referenced in a TABLE request. Projection reduces the volume of data returned from the RDBMS, which, in turn, improves application response time.