I am excited to share this information. Before I say anything, I want you to see below screenshot.
This is a background job information of two programs namely ZSD_LOAD and YSD_LOAD. Both the programs have similar functionality. ZSD_LOAD took 20,565 secs where as YSD_LOAD has taken 1,000 secs.
20,565 secs ~ 5 hrs to 1000 secs ~ 16.6 mins
Background: We have a program ZSD_LOAD that extracts 70K + records from a Ztable and fetch respective prices from its Price Condition tables.
We have used “For All entries (FAE)” to fetch condition numbers for each access table for all records from Ztable. [ Access tables are dynamically evaluated based on price condition maintained in the variants table. I do not see a possibility of Joins, as I need to pick lowest price from all available prices from all access tables of all existing price condition types ]
When we scheduled this program in the background, it took around 20K+ secs. Just like the duration mentioned in the above screenshot.
Ground Work: We felt that 5hrs for 70K + records is too bad, then we did ST05, SAT and ST12. We figured that “For all entries” is taking 99.59% of time. We have searched the possibilities to reduce the time, and then we have found one SAP Note that talk on these lines.
SAP Note 1662726 – Optimization of select with FOR ALL ENTRIES on SAP HANA database
My Code Changes :
Existing code- SELECT * FROM (tabname) INTO TABLE <gt_tab> FOR ALL ENTRIES IN Ztable WHERE kappl = 'A' AND kschl IN ('ZPR0',ZPR1','ZPR2') AND (gt_where) AND datbi GE Ztable-date AND datab LE Ztable-date. New Code: SELECT * FROM (tabname) INTO TABLE <gt_tab> FOR ALL ENTRIES IN Ztable WHERE kappl = Ztable-kappl AND kschl = Ztable-kschl AND (gt_where) %_HINTS HDB l_hint.
As per Note, We need to maintain only “EQ” operations in where conditions and maintain constants as fields of the internal table [eg- Ztable-kappl instead of ‘A’]. Populate l_hint as mentioned in the SAP Note.
Conclusion: After implementing the hints as mentioned in the SAP Note, the duration for “For all entries (FAE)” came down drastically. It is not necessary to implement hints for every “FAE” though.
Note – We are on SAP NW 750 EHP8 with HANA DB 1 patch 22.