Optimizing Sorts

Next, if the request specifies a sort operation, the data adapter must determine how to sort the data. The data adapter transfers the sort operation to the RDBMS only when:

• It can generate a single SQL statement.
• Data adapter-managed join optimization is not in effect.

For a discussion of these conditions, see Optimizing Joins.

Under these conditions, the data adapter invokes one of the following types of RDBMS-managed sorting:

1. If the request does not use the direct operators FST. and LST., the data adapter translates sort phrases (BY or ACROSS) into SQL ORDER BY clauses, thus passing responsibility for the primary sorting of data to the RDBMS. Note that BY TOTAL phrases are not optimized.

2. When the data adapter determines that it will retrieve at most one segment instance per sort break, it translates FOCUS FST. and LST. operators to SQL MIN and MAX operators and translates sort phrases (BY and ACROSS) into SQL ORDER BY clauses. The data adapter applies this technique in requests that explicitly specify the FST. or LST. operators and in requests that implicitly use them by referencing a non-numeric field in a report heading or footing.

FOCUS FST. and LST. operators retrieve the first or last segment instance per sort break. By definition, if a FOCUS request includes FST. or LST. operators on multiple fields from one segment, each field value displayed on the resulting report must come from the same instance of that segment. SQL MIN and MAX operators do not dictate that the resulting fields all come from the same segment instance.

Therefore, before translating the FST. and LST. operators in a FOCUS request to SQL MIN and MAX operators, the data adapter must ascertain that it will retrieve at most one segment instance per sort break. It makes this determination by analyzing the sort fields in the request, the primary key of each segment in the join structure, and any join fields that are components of the primary keys.

One segment instance is returned per sort break in any of the following situations:

• The sort field includes a segment's entire primary key.
• The sort field is a partial key joined to another sort field consisting of the remaining primary key component.
• The request includes only one FST. or LST. operator on a segment.

3. If the report request contains FST. or LST. operators on a segment that may return multiple instances, the data adapter directs the RDBMS to sort the data by primary key in the sequence specified by the Access File KEYORDER attribute. From this, the data adapter retrieves column values for the logically first (FST.) or last (LST.) key values of the returned data. After the RDBMS sort, FOCUS re-sorts the data according to the sort phrases.

Note:

LST. processing is invoked for SUM or WRITE operations involving alphanumeric or date fields and for alphanumeric or date fields in a report heading or footing.

From a performance standpoint, consider using the TABLEF command in conjunction with RDBMS-managed sorting to free FOCUS from having to verify the sort order. Refer to Advanced Reporting Techniques, for the TABLEF command.