OpenEdge DataServers : Performance Improvement for inner join query having FIRST/LAST construct at any join level
Performance Improvement for inner join query having FIRST/LAST construct at any join level
Starting in this release with the DataServer for MS SQL Server, ABL inner join queries, having one FIRST or LAST criteria for a child table at any join level, are optimized to translate into a single join SQL statement that is evaluated on server. This could result in significant performance improvements for FIRST/LAST criteria specified at any level of an ABL inner join query.
An example of an ABL inner join query evaluated on the server is shown below. In the example, the FIRST construct is at the innermost join level, and the remaining buffers are joined using EACH statements. One join SQL statement is generated for this query, and it is evaluated on the server side by the MS SQL server database.
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.
