Technical Articles

HITESH GAVANDE

July 29, 2019 1 minute read

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.

8 Comments

You must be Logged on to comment or reply to a post.

Matthew Billingham

July 29, 2019 at 6:17 am

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.

HITESH GAVANDE

Blog Post Author

July 29, 2019 at 6:38 am

Hello,

Thanks for comment!

yes,right!!! This should be only used with time consuming and expensive SELECT….FOR ALL ENTRIES statements.

Sandra Rossi

July 29, 2019 at 8:12 am

I also see in the note 1662726 ("Optimization of select with FOR ALL ENTRIES on SAP HANA database") you mention (thanks for that), that the solution proposed in this blog post is "ignored" if the profile parameter rsdb/prefer_join_with_fda = 1 ("Fast Data Access optimization for FOR ALL ENTRIES").

Michelle Crapo

July 29, 2019 at 11:54 am

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

Rajesh Tambe

August 7, 2019 at 3:59 am

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.

Minal Patel

August 30, 2019 at 9:16 am

Hi Hitesh,

Nice blog, Thanks for sharing.

Prince Joshi

December 19, 2019 at 10:14 am

Hi Hitesh,

thanks for you detailed blog!!

I am on SAP S4 HANA 1809 version with SAP netweaver 7.53.

I did the same as you mentioned in the blog but I noticed the usual 'FOR ALL ENTRIES' statement is having better performance than the so called optimized FOR ALL ENTRIES(using %_HINTS) statement.

I have executed the report for multiple iterations to get an average time difference between usual 'for all entries' and 'optimized(using %_HINTS) for all entries' for the select query.

Could you please tell me If I am missing something?

Just for information: I have checked profile parameter in Tcode: RZ11 rsdb/prefer_join_with_fda = 1 set.

*--------------------------------------------------------------------*
*-----------------------declarations---------------------------------*
*--------------------------------------------------------------------*
DATA: l_t_tablnm TYPE rsdu_t_tablnm,
      l_lines    TYPE i,
      l_hint     TYPE rsdu_hint.

START-OF-SELECTION.
*fetch header details
  SELECT * FROM cdhdr INTO TABLE @DATA(lt_cdhdr).

  IF lt_cdhdr IS NOT INITIAL.
    GET RUN TIME FIELD DATA(t1).

    SELECT * FROM cdpos
             INTO TABLE @DATA(lt_cdpos)
             FOR ALL ENTRIES IN @lt_cdhdr
             WHERE changenr = @lt_cdhdr-changenr.

    GET RUN TIME FIELD DATA(t2).

    DATA(t3)     = t2 - t1.
    DATA(lines1) = lines( lt_cdpos ).
    WRITE: /'Time taken by Usual for all entries is                ', t3,'in microseconds with ', 'no of entries fetched:', lines1.
  ENDIF.

*--------------------------------------------------------------------*
*-------------------Optimized for all entries------------------------*
*--------------------------------------------------------------------*
  APPEND 'CDPOS' TO l_t_tablnm.

  CALL FUNCTION 'RSDU_CREATE_HINT_FAE'
    EXPORTING
      i_t_tablnm   = l_t_tablnm
      i_fae_fields = 3
      i_fae_lines  = 50000
      i_equi_join  = 'X'
    IMPORTING
      e_hint       = l_hint
    EXCEPTIONS
      OTHERS       = 0.

*get start time
  GET RUN TIME FIELD DATA(t4).

*use HINTS
  SELECT * FROM cdpos
           INTO TABLE lt_cdpos
           FOR ALL ENTRIES IN lt_cdhdr
           WHERE changenr = lt_cdhdr-changenr
           %_HINTS HDB l_hint.

*get end time
  GET RUN TIME FIELD DATA(t5).

*get time difference
  DATA(t6) = t5 - t4.

*get no of entries fetched
  lines1 = lines( lt_cdpos ).
  WRITE: /'Time taken by optimized for all entries using HINTS is', t6, 'in microseconds with ' ,'no of entries fetched:', lines1.

Result of the report:

Regards,

Prince

HITESH GAVANDE

December 23, 2019 at 9:14 am

Hello Prince,

Thanks for comment.

I have run same program in different systems.

System 1:

System 2:

As you can see for system 1 have good performance over system 2.

As per SNOTE 1662726,

This optimization is available with SAP Kernel 7.43 and is activated by default when you use this or a higher SAP kernel version.

I believe for your version 7.53 this is by default setting …..so HINTS will not make much difference.

Kind Regards,

Hitesh

Technical Articles

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

Assigned Tags