The performance of open SQL as in ABAP 7.52

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.

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.