Optimizing Aggregation

FOCUS aggregation verbs SUM, COUNT, and WRITE, and direct operators MIN., MAX., and AVE., retrieve a final aggregated answer set rather than individual values. Since the RDBMS handles aggregation efficiently, the data adapter structures its retrieval request so that the RDBMS performs the aggregation. The data adapter passes aggregation to the RDBMS when:

• The IF or WHERE tests in the FOCUS request translate to SQL WHERE clauses. (Use STMTRACE, described in Tracing Data Adapter Processing, to examine the SQL generated from your request.)
• FOCUS generates a single SQL statement (as described in Optimizing Joins).
• Data adapter-managed join optimization is not in effect (see Optimizing Joins).
• The request specifies only the FOCUS SUM, COUNT, or WRITE aggregate verbs and the MIN., MAX., AVE., SUM., DST., and CNT. direct operators. Under certain conditions (described in Optimizing Sorts), the request can include the FOCUS FST. and LST. direct operators.
• The request does not reference DEFINE fields that do not comply with the definition of valued expressions (see Valued Expressions) or that are otherwise restricted (see SQL Limitations on Optimization of DEFINE Expressions).

Note:

FOCUS calculates COMPUTE fields on its internal matrix; they do not affect the data adapter's ability to pass requests for aggregation to the RDBMS.

The data adapter translates IF TOTAL and WHERE TOTAL tests to the SQL HAVING clause. It translates IF TOTAL and WHERE TOTAL tests on DEFINE and COMPUTE fields subject to the general limitations on the use of DEFINE in aggregation (see Valued Expressions).