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.
Okumaya devam et...
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.
Okumaya devam et...