Enforcing an index selection for a ProDataset child tables DATA-SOURCE query

Introduction

Child tables of a Business Entity dataset be filled using a number of query criteria during the ProDataset Fill process (in the Data Access class):

  • Data relation

  • Source Default Query for that table

  • Query string provided by the consumer

As this is a rather dynamic process to determine the correct index for the DATA-SOURCE query may be difficult at design time and usually it’s best to leave the index selection to the ABL query engine. However there are exceptions to this rule.

Assuming a Business Entity with eOrder and eOrderLine based on the Order and OrderLine tables of of the Sports2000 DB and a DATA-RELATION based on the OrderNum field:

 

Sample Dataset Definition
DEFINE DATASET dsOrder FOR eOrder, eOrderLine
    DATA-RELATION eOrdereOrderLineRelation FOR eOrder, eOrderLine
        RELATION-FIELDS (Ordernum,Ordernum)

Further assuming roughly 100 different Items in the Item table and 100.000’s of records in the OrderLine records.

When fetching records for the Order and records for the OrderLine table, the ProDataset processes Order records based on the selection for the Order records and then for each Order record the related OrderLine records based on the OrderNum field (the DATA-RELATION field). This typically uses the OrderLine index (consisting of the OrderNum and OrderLine fields). This index provides very good performance for selecting the OrderLines of the current Order record.

However when adding a selection on the OrderLine.ItemNum field (for ItemNum = 3 in this sample) to the Query the effective DATA-SOURCE’s query string for the eOrderLine temp-table becomes

FOR EACH eOrderLine WHERE eOrderLine.OrderNum = eOrder.OrderNum AND eOrderLine.ItemNum = 3

For this query both the OrderLine Index (on the OrderNum and OrderLine fields) and the ItemNum Index (on the ItemNum field) provide one equality match and thus are equally well for the ABL query optimizer. As the query optimizer cannot make any other decision he decides to use the ItemNum index as this index is alphabetically the first index of the two (the ABL query optimizer simply optimizes based on index fields in the query, not on data or key value distribution).

Unfortunately this index choice leads to a lots of record reads and a very poor performance for this query. The index selection can be verified by testing the

BUFFER eOrderLine:DATA-SOURCE:QUERY:INDEX-INFORMATION(1) 

and

BUFFER eOrderLine:DATA-SOURCE:QUERY:PREPARE-STRING

in the ProDataset’s BEFORE-FILL call back.

One solution would be to add a USE-INDEX to the SourceDefaultQuery for the eOrderLine table.  However that would enforce the index in every case: and in the case of just querying the eOrderLine table (without querying the eOrder table) the OrderLine index might not be a very good choice…

For this purpose we have added the SuggestDataSourceIndex method to the DataAccess base class. This method allows to ABL developer to specify the index to be used when a ProDataset buffer (a child buffer of a DATA-RELATION) is filled using the DATA-RELATION (thus the parent buffer and the child-buffer being filled).

This allows to specify an index for the case that a table is filled as a child table and leaving the index selection to the ABL in case a child records is queried on its own.

The SuggestDataSourceIndex method makes use of the QueryHelper’s SetUseIndex method and for ease of use only processes the first DATA-SOURCE buffer (in case of a joined query as the DATA-SOURCE). This is because we assume that in the vast majority of cases it’s only going to be the index selection on a DATA-SOURCE’s first buffer that is performance critical.

Sample implementation

The following sample is based on the Scenario described above: The eOrderLine table should always be FILL'ed using the OrderLine index when used as a child buffer. And let the AVM determine the perfect index alone when the eOrderLine table is filled on it's own.

In the DefineReadEvents method we subscribe to the BEFORE-FILL callback of the ProDataset.

Sample DefineReadEvents method
    /*------------------------------------------------------------------------------
        Purpose: TO-DO: Subscribe to ProDataset Event Handlers using SET-CALLBACK
                 as needed                                                                      
        Notes:   Overrides ABSTRACT method in Consultingwerk.OERA.DataAccess,
                 Invoked in FetchData, note that the callback methods need to be 
                 PUBLIC and need to have the Dataset defined as an INPUT Parameter.
                 Errors thrown from or raised in the FILL call-back methods of the 
                 DataAccess class (SET-CALLBACK) will stop the Dataset from getting 
                 filled / filled further. However errors thrown from the call-back 
                 methods are not thrown from the FILL() method further up into a 
                 catch block or to the client.  
                 See http://knowledgebase.progress.com/articles/Article/P188375
        Sample:  BUFFER eCustomer:SET-CALLBACK (Consultingwerk.DatasetBufferCallbackEnum:AfterFill,
                                                "eCustomerAfterRowFill":U) .                 
    ------------------------------------------------------------------------------*/
    METHOD OVERRIDE PROTECTED VOID DefineReadEvents ():        
        DATASET dsOrder:SET-CALLBACK ("BEFORE-FILL":U, "dsOrderBeforeFill":U, THIS-OBJECT).
    END METHOD.

The dsOrderBeforeFill method acting as the BEFORE-FILL callback for the dataset does now provide the index hint:

dsBeforeFill method
    /*------------------------------------------------------------------------------
        Purpose: Callback method for the BEFORE-FILL event of dsOrder
        Notes:   Errors thrown from or raised in the FILL call-back methods of the 
                 DataAccess class (SET-CALLBACK) will stop the Dataset from getting 
                 filled / filled further. However errors thrown from the call-back 
                 methods are not thrown from the FILL() method further up into a 
                 catch block or to the client.  
                 See http://knowledgebase.progress.com/articles/Article/P188375
        @param dsOrder The Dataset   
    ------------------------------------------------------------------------------*/
    METHOD PUBLIC VOID dsOrderBeforeFill (DATASET dsOrder):
        THIS-OBJECT:SuggestDataSourceIndex (BUFFER eOrderLine:HANDLE, "orderline":U) .
    END METHOD .

Note, that in the dsOrderBeforeFill method it it possible to parse the current query string (provided by the consumer based on the THIS-OBJECT:FetchDataRequest:Queries property.