Skip to Content
Technical Articles

ABAP: FOR ALL ENTRIES Performance after upgrade

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.

1 Comment
You must be Logged on to comment or reply to a post.
  • Maybe you should read this blog:¬†https://blogs.sap.com/2018/12/09/for-all-entries-gtt-and-open-sql/

    Also, there is a note at support.sap.com that tells you what hints to apply to a select with FOR ALL ENTRIES, with HANA.