Wednesday, 15 November 2017

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.

SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Certifications

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.

SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Certifications

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. 
SAP ABAP Tutorials and Materials, SAP ABAP Guides, SAP ABAP Certifications

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.

2 comments:

  1. Hi.

    Please correct the program as I see you are using BELNR = GJAHR on the inner join selects. Please correct that and run as I see Inner join takes more time than for all entries.

    ReplyDelete
  2. Thanks for short demo on the for all entries vs Inner join. But please check and post back with your results again. The first two selects were not fetching any data. Therefore the run time for inner joins are short.

    ReplyDelete