ABAP: FOR ALL ENTRIES Performance after upgrade

[support1234]

Problem

After a Netweaver/HANA upgrade, BW transformations/DTP's experienced a huge performance loss. Before upgrade, a process would take 10 minutes, after upgrade 2 days.

Root cause

After analysis, the cause was identified being the FOR ALL ENTRIES IN statement, using the <= qualifier.

Example

In this example the latest status of a record key is determined.

SELECT KEY1, DATE1, FIELD1

FROM   [LOOKUP_TABLE] INTO TABLE [ITAB]

FOR ALL ENTRIES IN [SOURCE_TABLE]

WHERE [LOOKUP_TABLE].KEY1 = [SOURCE_TABLE].KEY1

AND   [LOOKUP_TABLE].DATE1 <= [SOURCE_TABLE].DATE1.



SORT [ITAB] BY KEY1, DATE1 DESCENDING.

DELETE ADJACENT DUPLICATES FROM [ITAB] COMPARING KEY1.

This works fine in situations before 7.5, but in NW 7.5 performance is degraded.

Solution

Put [SOURCE_TABLE].DATE1 in a variable, and compare with that variable, so:

READ TABLE [SOURCE_TABLE] INTO [SOURCE_FIELDS] INDEX 1.



SELECT KEY1, DATE1, FIELD1

FROM   [LOOKUP_TABLE] INTO TABLE [ITAB]

FOR ALL ENTRIES IN [SOURCE_TABLE]

WHERE [LOOKUP_TABLE].KEY1 = [SOURCE_TABLE].KEY1

AND   [LOOKUP_TABLE].DATE1 <= [SOURCE_FIELDS].DATE1.



SORT [ITAB] BY KEY1, DATE1 DESCENDING.

DELETE ADJACENT DUPLICATES FROM [ITAB] COMPARING KEY1.

That solved the problem, but.... this was used extensively in the code. A first scan of RSAABAP revealed >700 places where a FOR ALL ENTRIES IN was used.

A custom ABAP was developed by me, identifying only the above situation, now leading to 70 places. This was modified in the code, and no further problems were found in go-live.

Note

Of course this only works if [SOURCE_FIELDS].DATE1 is a constant for the package being processed, if this is not the case, consider using the DATE1 in a sematic group, or put DATE1 in a separate table and loop over that table.