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.

 

 

 

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !