Skip to Content

The use of FOR ALL ENTRIES in Open SQL of ABAP has been used for very long time. And it is helpful for report data from different table.

Nevertheless, FOR ALL ENTRIES has two fallback.

1. It should check whether the SQL condition internal table is initial before check. Or else, ABAP will have very big performance issue since it will read all the data from one table.

2. If SQL condition internal table has too many entries, the performance is also very bad since ABAP will convert the FOR ALL ENTRIES.

One interesting new feature from ABAP 7.52 can fix these two fallback. You can refer detail as Alternative 4 of below link.

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/abapselect_data_source.htm

I have check the performance of these different ways of SQL. Three ways we compare are as below.

  • FOR ALL ENTRIES
  • Open SQL use internal table as data source.
  SELECT item~rbukrs, item~gjahr, item~belnr  FROM acdoca AS item INNER JOIN @lt_bkpf AS head
    ON item~rldnr = '0L'
    AND item~rbukrs = head~bukrs
    AND item~gjahr = head~gjahr
    AND item~belnr = head~gjahr
  INTO TABLE @lt_acdoca     BYPASSING BUFFER.
  • Open SQL use internal table as data source, but the internal table is sorted table
  TYPES tt_bkpf_sort TYPE SORTED TABLE OF ts_bkpf WITH UNIQUE KEY bukrs gjahr belnr.
  DATA lt_bkpf_sort TYPE tt_bkpf_sort.
  SORT  lt_bkpf BY bukrs gjahr belnr.
  lt_bkpf_sort = CORRESPONDING #( lt_bkpf ).

  SELECT item~rbukrs, item~gjahr, item~belnr
    FROM acdoca AS item INNER JOIN @lt_bkpf_sort AS head
    ON item~rldnr = '0L'
    AND item~rbukrs = head~bukrs
    AND item~gjahr = head~gjahr
    AND item~belnr = head~gjahr
  INTO TABLE @data(lt_acdoca) BYPASSING BUFFER.
 

 

Based on S4HANA 1709. Three ways running time is as below.

From this chart, we can have conclusions as below:

  1. While SQL condition size not big, the running times do not have big difference.
  2. While SQL condition size is bigger ( > 10 000 ), FOR ALL ENTRIES will use more time than other two SQL. It will have worse performance.
  3. While the SQL size grows, the running time of FOR ALL ENTRIES will grow greatly, its performance will be much worse.

Since FOR ALL ENTRIES has much difference, we can compare the other solutions.

From this chart, we can get several conclusions.

  1. the running time of Open SQL will also grows together with size of internal table of Open SQL data source.
  2. Although there is not big difference between these two solution, actually the internal table with sorted key is better.
  3. Nevertheless, extended check ( SLIN ) will require the data source of open internal table with certain key fields.

From these testing, we can make a conclusion. If you are programming in higher version of ABAP 7.52, higher than 1709, you can use alternative solution than FOR ALL ENTRIES in report logic.

 

For reference, the performance testing program.

REPORT <Report name>.

PARAMETERS p_rows TYPE i DEFAULT 1000.

START-OF-SELECTION.

  SELECT * FROM bkpf UP TO @p_rows ROWS INTO TABLE @DATA(lt_bkpf).

*option 3 from sortale tables
  TYPES: BEGIN OF ts_bkpf,
           bukrs TYPE bkpf-bukrs,
           gjahr TYPE bkpf-gjahr,
           belnr TYPE bkpf-belnr,
         END  OF ts_bkpf
        t_bkpf_sort TYPE SORTED TABLE OF ts_bkpf WITH UNIQUE KEY bukrs gjahr belnr.
  DATA lt_bkpf_sort TYPE tt_bkpf_sort.
  SORT  lt_bkpf BY bukrs gjahr belnr.
  lt_bkpf_sort = CORRESPONDING #( lt_bkpf ).

  GET RUN TIME FIELD data(lv_start).
  SELECT item~rbukrs, item~gjahr, item~belnr
    FROM acdoca AS item INNER JOIN @lt_bkpf_sort AS head
    ON item~rldnr = '0L'
    AND item~rbukrs = head~bukrs
    AND item~gjahr = head~gja .
  TYPES thr
    AND item~belnr = head~gjahr
  INTO TABLE @data(lt_acdoca) BYPASSING BUFFER.
  GET RUN TIME FIELD data(lv_end).
  data(lv_dura) = lv_end - lv_start.

  WRITE: / 'time for sorted interal table as Open SQL data source', lv_dura.

  " Option 2 from internal table
  GET RUN TIME FIELD lv_start.
  SELECT item~rbukrs, item~gjahr, item~belnr  FROM acdoca AS item INNER JOIN @lt_bkpf AS head
    ON item~rldnr = '0L'
    AND item~rbukrs = head~bukrs
    AND item~gjahr = head~gjahr
    AND item~belnr = head~gjahr
  INTO TABLE @lt_acdoca     BYPASSING BUFFER.
  GET RUN TIME FIELD lv_end.
  lv_dura = lv_end - lv_start.

  WRITE: / 'time for interal table as Open SQL data source', lv_dura.


    " opiton 1 FOR ALL entries
  IF lt_bkpf IS NOT INITIAL.
    GET RUN TIME FIELD lv_start.

    SELECT rbukrs, gjahr, belnr FROM acdoca
      INTO TABLE @lt_acdoca     BYPASSING BUFFER
      FOR ALL ENTRIES IN @lt_bkpf
      WHERE rldnr = '0L'
        AND rbukrs = @lt_bkpf-bukrs
        AND gjahr = @lt_bkpf-gjahr
        AND belnr = @lt_bkpf-belnr
    .
    GET RUN TIME FIELD lv_end.
  ENDIF.
  lv_dura = lv_end - lv_start.

  WRITE: / 'time for all entries', lv_dura.

 

 

 

To report this post you need to login first.

8 Comments

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

  1. Matthew Billingham

    FOR ALL ENTRIES should of course only ever be used as a last resort. It’s nearly always better to use an INNER JOIN.

    I only ever use it in BW routines, where I’m supplied with an internal table that I need to join to. I’m looking forward to 7.52 so I can switch… and never use FAE again!

    (1) 
  2. Thomas Liebl

    Hello Steven

    You should check your statements on DB level – compare the Execution Plan (EXPLAIN PLAN) and see the difference depending on your statement size.

    FOR ALL ENTRIES has many facets concerning execution performance and they are controlled by many parameters and also differ by kernel release level.

    Also check this answer by Frank-Martin Haas: https://archive.sap.com/discussions/thread/812436

    And some notes (I guess you are using HANA db – means parameter “Fast Data Access Optimization for FOR ALL ENTRIES”):

    1662726 – Optimization of select with FOR ALL ENTRIES on SAP HANA database
    1987132 – SAP HANA: Parameter setting for SELECT FOR ALL ENTRIES

    best regards, Tom

    (1) 
  3. Michelle Crapo

    Hi Steven!!!

    My beloved for all entries.   It has been discussed many times.   Basically the answer to the question was always use a join.

    I honestly think FAE has a place and a time.   But not very often, and it was used too much in earlier versions of SAP.

    A secret from me to you – I sometimes use FAE.    It’s rare, but sometimes.   And testing performance time is very tricky.

    I did enjoy your blog!

    Michelle

    (1) 
    1. Steven Lu Post author

      Thanks for your comment. So far as my mind., two limitations exist for this syntax:

      1. Version 1709 is a little new, there are not too much projects up to now.

      2. Only 1 internal table as data source can be used in one SQL.

      But if in this limitation, I really like this usage.

      by the way, if we join two database tables, CDS View is better solution to include all business logic.

      If we join internal table and DB table, this usage looks cool.

      if we join two internal tables, CORRESPONDING is better.

       

      (1) 
    1. Steven Lu Post author

       

      Dear Themte,

       

      Glad to know you like this post.

      Actually, it is syntax of ABAP 7.52. And you can use it in 1610 or other  S4HANA systems if you can update the kernel.
      The following is SAP kernel release and ABAP release. You can check with Basis, if he can upgrade the kernel to 7.53, the new syntax can be used.

       

      By the way, SAP version too low, for example, Suit on HANA, you can check with Basis whether you can upgrade or not.

      Or even lower, for example, ECC 6, maybe database is not HANA, and it is sure you cannot use the new syntax.

      Regards
      Steven

      Reference

      Best Practices for ABAP Development on SAP Netweaver 7.5x

      How to check version of kernel?

       

      (0) 

Leave a Reply