Performance optimization: how to check if index is used?

While developing complex BW transformations where the data manipulation is involved using ABAP - performance is crucial.  The transformations must perform as fast much as possible. This is because in future there may be a lot of data and even it run smoothly now it may not be the case in future.

It is very common in BW to use look-ups of data (e.g. from DSO objects) while data transformation happens. Once we retrieve the data from DSO for sake of look-up tables we shall involve DSO’s table index within the SELECT statement. The index makes data retrieval much faster than w/o usage of index. In case of every transparent database table there is so called primary index in place. This index comprises of all key fields within the table.

To employ usage of the index by SELECT statement you need to ensure following. WHERE condition of the SELECT must contain as much fields from the index as possible. Or let’s say key fields of the table in case of primary index.

E.g. my DSO (C*) below (its active table is /BIC/AC*00) has following primary keys; 0MATERIAL, 0PLANT, 0ORDERITEM, 0BUS_EVENT, 0PRODORDER, 0FISCVARNT

     

Now let’s have a look into two SELECT statements. One which does utilize the primary index on top of C* DSO and other one which does’t?

    SELECT plant prodorder gr_qty planordqty
      INTO TABLE lt_mat
      FROM /bic/acxxxx00
      FOR ALL ENTRIES IN result_package
      WHERE prodorder = result_package-prodorder
        AND plant     = result_package-plant.

    SELECT plant prodorder gr_qty planordqty
      INTO TABLE lt_mat
      FROM /bic/acxxxx00
      FOR ALL ENTRIES IN result_package
      WHERE material  = result_package-material
        AND plant     = result_package-plant
        AND prodorder = result_package-prodorder
        AND fiscvarnt = result_package-fiscvarnt.

Can you guess which SELECT is it? It is the second one. Actually in the first one there is first field from index 0MATERIAL missing in the WHERE condition. Therefore database optimizer will not use the primary index for the search operation and a full table scan is performed. In case of second SELECT there are four out of six fields are used in WHERE therefore optimized will use primary index.

Finally we would like to know if index was really used during our BW load. What can be done is that before kicking out the load we start SQL trace. This is done in TA ST05. You can set trace with filter on e.g. your user name and table from where we retrieving the data. Then just run a load. After the load is finished deactivate the trace and go to Display Trace in TA ST05. Display the trace result and for operations like OPEN or FETCH click Explain button from the toolbar. You will get a screen similar to below screenshot. Picture on left side shows situation with my 2^nd SELECT – index was used and on right side is my 1^st SELECT – on index usage. We can clearly recognize by key words INDEX RANGE SCAN that index was used. Also notice what estimated costs (e.g. CPU) are for both SELECTS.

One more point regarding how system finds suitable indices for particular SQL SELECT. If WHERE condition is written in same sequence as key are specified then index is found faster. Therefore always try to align your WHERE condition as per sequence of index’s fields.

More information can be found:

How to Check if an Index is Used

Indexing in SAP Tables

Performance optimization of SQL statements in ABAP