Performance Lookups in BW Transformation – Finding the relevant records

After we have dealt with the relevant selection techniques and with the various types of internal tables, the most important performance optimisations are initially ensured for the lookups, in our BW transformations.

However, this does not completely cover the topic: Because until now we have assumed that only the relevant information will be searched in our lookup tables. But how can we ensure this?

Using our example, this article clarifies how only the relevant data sets are selected from database table Y, to search in the internal table Z. The size of database table Y is crucially important here. If in the transformation the data packages contain 50,000 data sets, but the Y table contains millions of data sets, it must be ensured that this will not be completed searched for every data package.

In order to guarantee this, we use the following procedure. In the SELECTION from the database, we use the instruction FOR ALL ENTRIES. Thereby, the following points must be adhered to:

1. In order to carry out efficiently FOR ALL ENTRIES, it is vital to always request the complete key for the LOOKUP table to be read.
2. FOR ALL ENTRIES only provides clear key values (see DISTINCT)
3. If FOR ALL ENTRIES is used, the data can only be requested ordered from the database, with the addition PRIMARY KEY (instruction: ORDER BY). 

Also, when using this addition, there are several restrictions that must be adhered to. Amongst other things, SINGLES, UNIONS and GROUP BY selections cannot be carried out.

As FOR ALL ENTRIES is not a standard SQL instruction, but is a SAP extension, in the processing of the various databases, ultimately the instruction will be translated from the ABAP-Stack into the standards of SQL. In the case of very complex FOR ALL ENTRIES, there are further optimisation options via parametrisations or instructions for this reason. If you are using a SAP HANA database, even a function module (RSDU_CREATE_HINT_FAE) can be used, which prepares the optimum implementation of FOR ALL ENTRIES . You can find further helpful information on the topic from the following SAP references:

1. 1622681 - DBSL hints for SAP HANA
2. 1987132 - SAP HANA: Parameter setting for SELECT FOR ALL ENTRIES

In the next article, we will focus on the often necessary aggregation and/or compression of inconclusive data sets. This arises, for example, if information selected from the database has to be divided into various groups.