Improving join performance for queries with FIRST or LAST criteria

In versions prior to OpenEdge 11.3, processing an ABL query with multiple tables using the ABL FIRST and LAST keywords was significantly slow compared to processing a similar ABL query against a single table or against a join query that did not use FIRST or LAST criteria. The performance degradation was because the server join was unable to reduce the FIRST or LAST criteria to a single server join record during ABL to SQL translation. This widened the result set and forced client selection, and/or the client was forced to retrieve server records key by key using multiple queries.

The join performance has been improved by ensuring that all joins with FIRST or LAST criteria translate ABL to a single server join SQL statement, thus maximizing server optimization, minimizing client selection, and reducing round trips to the foreign data source.

The ABL uses an index to evaluate the “FIRST” or “LAST” criteria. By default, the primary index is selected in determining the “MIN” or “MAX” column criteria to be applied in the server sub-query that handles the FIRST or LAST join criteria. When USE-INDEX clause is used, the index specified in the USE-INDEX clause will be used in place of the default primary index. Please visit Database index key widths section to view examples that illustrate the behavior of the index definition if the single-shot query mechanism is used.

Note:

Single-shot Server side Join selection is excluded for the scenarios listed below:

  • When the ABL queries have the BY clause in it.
  • When the ABL queries have multiple single shot statements.
  • When the ABL queries have OUTER-JOIN.