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

The join performance has now been improved by ensuring that all joins with FIRST or LAST criteria translate ABL to a single server join SQL statement. This functionality is known as Single-shot functionality.

From Progress OpenEdge 11.7.4 release onwards, the single-shot statement will now be evaluated at the server side.This will help in performance improvement of single-shot joins by:
  • Maximizing server optimization
  • Minimizing client selection
  • Reducing round trips to the foreign data source

An example of single-shot at innermost join level is shown below. Here, single-shot is at innermost join level and remaining buffers are joined using EACH statement. It will be evaluated at server side.


FOR EACH customer,
    EACH order OF customer,
    EACH orderline OF order,                          
    FIRST ITEM OF orderline:
    DISPLAY customer.custnum  order.custnum order.ordernum
    orderline.linenum item.itemnum.
END.

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.

Index definitions support ABL USE-INDEX modifier. ABL translates USE-INDEX to ORDER BY for DataServer operations. For example, if you define custnum as an index on the custnum field without using the single shot query mechanism, the following ABL statements are equivalent when accessing a SQL database: 

FOR EACH customer USE-INDEX custnum:
 
FOR EACH customer BY custnum:

The following examples illustrate the behavior of the index definition if the single-shot query mechanism is used.

If single-shot query mechanism is used, the following query results in a server-side join:

FOR EACH customer NO-LOCK USE-INDEX custnum WHERE custnum = 1,
FIRST order OF customer USE-INDEX custorder:

If the single-shot query mechanism is used, the following query results in a client-side join as the BY clause has been used:

FOR EACH customer NO-LOCK WHERE custnum = 1,
FIRST order OF customer 
BY customer.custnum BY order.custnum BY order.ordernum:
Note:

If you do not specify USE-INDEX, your query will return records in an unpredictable order. Your application might not require predictable ordering, but if it does, be sure to include USE-INDEX in your query definition.

Progress recommends that you use a unique index as a USE-INDEX modifier when using it with a FOR EACH query that joins FIRST or LAST on a child table. If the index is not unique, join is still evaluated on client side.

Client startup parameter and QUERY-TUNING Switch

Single-shot evaluation on server will be ON by default. It can be turned OFF and evaluated at client side by using client startup parameter -nojoinbysqldb. It can also be turned OFF at run time with QUERY-TUNING switch NO-JOIN-BY-SQLDB.