When “For All Entries(FAE)” gone for a Toss
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.
I'm not sure why you would conclude the above, but by looking at the problem based solely on what you describe, my choice would have been using some inner join selection, and taking advantage of either secondary indexes, or primary keys.
Hi Former Member,
My point is when you go for FAE, FAE turned to be devastating then try for hints, it might get you back into the game.
Well, it depends upon the requirement whether one go for FAE or joins or some other technique. My requirement needs FAE.
Regards,
Siva
I wonder how would the SELECT run without the hint line?
Because SELECTs are pretty different even without the hint...