Skip to Content
Technical Articles

SAP ABAP/BI : ‘SELECT…FOR ALL ENTRIES’ Optimization

Dear Readers,

This blog can be use to optimize Performance of SELECT….FOR ALL ENTRIES statements which are taking more time to execute.(Implementation of Note 1662726)

Pre-requisite: SAP DB should be HANA DB.

With the use of this optimization techniques we can improve execution time of time consuming and expensive  SELECT….FOR ALL ENTRIES statements.

Code Changes Needs to be implemented simply in 2 steps:

Step 1) Call FM RSDU_CREATE_HINT_FAE

Step 2) We can use HANA DB HINTS from above FM while calling SELECT… FOR ALL ENTRIES   statement.

So DB HINTS imported from FM RSDU_CREATE_HINT_FAE  should be used in SELECT… FOR ALL ENTRIES Statement.

Please find below run-time comparison between “Normal For All Entries statement” and “HANA Optimized For All Entries” :

SELECT… FOR ALL ENTRIES Statement :

 

HANA Optimized SELECT… FOR ALL ENTRIES Statement :

 

Run Time Comparison : 

So , with ‘Normal For All Entries statement’ , we need 21.62 seconds

and with the use of ‘HANA Optimized For All Entries statement’, we need 1.97 seconds.

So, with the current scenario/example comparison, we able to improve more than 90 percent of execution time. 

So, with this optimization technique we can able to improve significant run time.

Thanks for your time 🙂

PS: I have used this in one of my END Routine code in SAP BI transformation.

6 Comments
You must be Logged on to comment or reply to a post.
  • This is covered in sap note 1662726. You should only use it when using FAE to a large dataset with the package within a BW routine. Otherwise you should use a JOIN. If you’re joining to a small data set, you should read the data (or a subset) into a HASHED table and read from that to populate your results set.

  • Interesting blog.   And as always – it has generated some great comments.  I had to read it as I thought FAE was dead.

    Here’s an interesting blog on some alternatives.

    With package size  FYI – I hadn’t used this for a while so I looked it up.

     

    DATA(out) = cl_demo_output=>new( ).
    SELECT carrid, connid, cityfrom, cityto
    FROM spfli
    ORDER BY carrid, connid, cityfrom, cityto
    INTO  TABLE @DATA(result) PACKAGE SIZE 10.
    out->next_section( |Package { sy-dbcnt / 10 }|
    )->write( result ).
    ENDSELECT.
    out->display( ).

    Michelle

  • Hello Hitesch,

    My data load earlier taking 3 hr to load due to FAE statements in SR and ER in transformation.

    But with the above technique , the same code taking 20 mins to run the whole transformation.

    Thanks for sharing.