ABAP Parallel cursor to improve Performance of your SAP reports
Implementing an ABAP parallel cursor in your SAP program has the potential to improve performance of your nested loop by a significant amount
Why is a Parallel Cursor important?
When you loop around an internal table within your ABAP code using a where clause the loop command still processes every row in the table to check if it meets the correct criteria.This is probably not a problem in many cases as it's the processing within the loop that takes the time rather than the actual loop, but having said that it's always a good idea to avoid any unnecessary processing.
There will also be a number of cases where you have thousands/millions of records but you know that based on your sort criteria only a few of them will match your where clause and be all together somewhere in the middle of the table. The following code demonstrates the use of a parallel cursor, which allows you to go straight to the first of these records and stop processing immediately after you have processed the last one. This can significantly reduce the processing time of your ABAP reports, especially those that have large itab loops or nested loops. The example below reduces the processing time of a very simple nested loop of KEKO and KEPH from 302 seconds to less than 1 seconds.
*&---------------------------------------------------------------------*
*& Report ZREP_PARALLEL_CURSOR
*&---------------------------------------------------------------------*
*&
*& SAP ABAP functionality
*& **********************
*& Select data from database table into internal table
*& Loop at internal table
*& Loop using Parallel cursor functionality
*& Write info to output report screen
*& SORT internal table
*& Calculate runtime of an ABAP report using GET RUN TIME FIELD
*& Convert GET RUN TIME FIELD to seconds
*&---------------------------------------------------------------------*
REPORT zrep_parallel_cursor.
REPORT zrep_parallel_cursor.
DATA: it_keko TYPE STANDARD TABLE OF keko,
wa_keko LIKE LINE OF it_keko,
it_keph TYPE STANDARD TABLE OF keph,
wa_keph LIKE LINE OF it_keph.
DATA: d_mat_cost TYPE keph-kst001,
d_lab_cost TYPE keph-kst004,
d_over_head TYPE keph-kst010,
d_ext_purch TYPE keph-kst014,
d_misc_cost TYPE keph-kst002,
ld_starttime TYPE i,
ld_endtime TYPE i,
ld_runtime TYPE i.
**************************************************************
* START-OF-SELECTION
START-OF-SELECTION.
SELECT *
INTO TABLE it_keko FROM keko.
SELECT *
INTO TABLE it_keph FROM keph.
* Table must be sorted to ensure all required records are together
SORT it_keph BY kalnr kalka bwvar kadky.
* Perform actual processing
PERFORM get_cost_values.
*----------------------------------------------------------------------*
FORM get_cost_values.
* Determine start position and then process all records for given key
* from that starting point
GET RUN TIME FIELD ld_starttime.
LOOP AT it_keko INTO wa_keko.
clear:d_mat_cost, d_lab_cost, d_over_head, d_ext_purch,d_misc_cost.
* it_keph is sorted on kalnr kalka bwvar kadky.
READ TABLE it_keph TRANSPORTING NO FIELDS WITH KEY kalnr = wa_keko-kalnr
kalka = wa_keko-kalka
bwvar = wa_keko-bwvar
kadky = wa_keko-kadky BINARY SEARCH.
IF sy-subrc = 0.
* Loop at itab from first record found (sy-tabix) until record
* no-longer matches your criteria.
LOOP AT it_keph INTO wa_keph FROM sy-tabix.
IF wa_keph-kalnr = wa_keko-kalnr AND wa_keph-kalka = wa_keko-kalka
AND wa_keph-bwvar = wa_keko-bwvar AND wa_keph-kadky = wa_keko-kadky.
* Key match
d_mat_cost = d_mat_cost + wa_keph-kst001.
d_lab_cost = d_lab_cost + wa_keph-kst004.
d_over_head = d_over_head + wa_keph-kst010.
d_ext_purch = d_ext_purch + wa_keph-kst014.
d_misc_cost = d_misc_cost + wa_keph-kst002 + wa_keph-kst003
+ wa_keph-kst005 + wa_keph-kst006 + wa_keph-kst007
+ wa_keph-kst008 + wa_keph-kst009 + wa_keph-kst011
+ wa_keph-kst012 + wa_keph-kst013 + wa_keph-kst015
+ wa_keph-kst016 + wa_keph-kst017 + wa_keph-kst018
+ wa_keph-kst019 + wa_keph-kst020 + wa_keph-kst021
+ wa_keph-kst022 + wa_keph-kst023 + wa_keph-kst024
+ wa_keph-kst025 + wa_keph-kst026 + wa_keph-kst027
+ wa_keph-kst028 + wa_keph-kst029 + wa_keph-kst030
+ wa_keph-kst031 + wa_keph-kst032 + wa_keph-kst033
+ wa_keph-kst034 + wa_keph-kst035 + wa_keph-kst036
+ wa_keph-kst037 + wa_keph-kst038 + wa_keph-kst039
+ wa_keph-kst040.
ELSE.
* End of required records
EXIT. " Exit loop
ENDIF.
ENDLOOP.
ENDIF.
IF wa_keko-losgr GE 1.
d_mat_cost = d_mat_cost / wa_keko-losgr.
d_lab_cost = d_lab_cost / wa_keko-losgr.
d_over_head = d_over_head / wa_keko-losgr.
d_ext_purch = d_ext_purch / wa_keko-losgr.
d_misc_cost = d_misc_cost / wa_keko-losgr.
check not d_mat_cost is INITIAL.
WRITE:/ d_mat_cost, d_lab_cost, d_over_head, d_ext_purch,d_misc_cost.
ENDIF.
ENDLOOP.
GET RUN TIME FIELD ld_endtime.
ld_runtime = ld_endtime - ld_starttime. "time in micro seconds
WRITE:/ ld_runtime, 'micro, nano or what ever seconds'.
ld_runtime = ld_runtime / 1000000. "time in seconds
WRITE:/ ld_runtime, 'seconds'.
ENDFORM. " GET_COST_VALUES
As you can see this code takes less than 1 second to execute
Same code not using parellel cursor increases the runtime from 1 to 302 seconds
If you then run this exact same example ABAP code but without the use of the parallel cursor functionality
you can see that the execution time increases significantly. Using the data on my test system which has
36,000 records in KEKO and 57,000 records in KEPH the runtime time goes from 1 second to 302 seconds,
which is massive and hopefully demonstrates the importance of using parallel cursors.
*&---------------------------------------------------------------------*
*& Report ZREP_NONE_PC
*&---------------------------------------------------------------------*
REPORT zrep_none_pc.
DATA: it_keko TYPE STANDARD TABLE OF keko,
wa_keko LIKE LINE OF it_keko,
it_keph TYPE STANDARD TABLE OF keph,
wa_keph LIKE LINE OF it_keph.
DATA: d_mat_cost TYPE keph-kst001,
d_lab_cost TYPE keph-kst004,
d_over_head TYPE keph-kst010,
d_ext_purch TYPE keph-kst014,
d_misc_cost TYPE keph-kst002,
ld_starttime TYPE i,
ld_endtime TYPE i,
ld_runtime TYPE i.
**************************************************************
* START-OF-SELECTION
START-OF-SELECTION.
SELECT *
INTO TABLE it_keko FROM keko.
* UP TO 20 rows.
SELECT *
INTO TABLE it_keph FROM keph.
* Table must be sorted to ensure all required records are together
SORT it_keph BY kalnr kalka bwvar kadky.
* Perform actual processing
PERFORM get_cost_values.
*----------------------------------------------------------------------*
FORM get_cost_values.
GET RUN TIME FIELD ld_starttime.
LOOP AT it_keko INTO wa_keko.
CLEAR:d_mat_cost, d_lab_cost, d_over_head, d_ext_purch,d_misc_cost.
LOOP AT it_keph INTO wa_keph WHERE kalnr = wa_keko-kalnr
AND kalka = wa_keko-kalka
AND bwvar = wa_keko-bwvar
AND kadky = wa_keko-kadky.
* Key match
d_mat_cost = d_mat_cost + wa_keph-kst001.
d_lab_cost = d_lab_cost + wa_keph-kst004.
d_over_head = d_over_head + wa_keph-kst010.
d_ext_purch = d_ext_purch + wa_keph-kst014.
d_misc_cost = d_misc_cost + wa_keph-kst002 + wa_keph-kst003
+ wa_keph-kst005 + wa_keph-kst006 + wa_keph-kst007
+ wa_keph-kst008 + wa_keph-kst009 + wa_keph-kst011
+ wa_keph-kst012 + wa_keph-kst013 + wa_keph-kst015
+ wa_keph-kst016 + wa_keph-kst017 + wa_keph-kst018
+ wa_keph-kst019 + wa_keph-kst020 + wa_keph-kst021
+ wa_keph-kst022 + wa_keph-kst023 + wa_keph-kst024
+ wa_keph-kst025 + wa_keph-kst026 + wa_keph-kst027
+ wa_keph-kst028 + wa_keph-kst029 + wa_keph-kst030
+ wa_keph-kst031 + wa_keph-kst032 + wa_keph-kst033
+ wa_keph-kst034 + wa_keph-kst035 + wa_keph-kst036
+ wa_keph-kst037 + wa_keph-kst038 + wa_keph-kst039
+ wa_keph-kst040.
ENDLOOP.
IF wa_keko-losgr GE 1.
d_mat_cost = d_mat_cost / wa_keko-losgr.
d_lab_cost = d_lab_cost / wa_keko-losgr.
d_over_head = d_over_head / wa_keko-losgr.
d_ext_purch = d_ext_purch / wa_keko-losgr.
d_misc_cost = d_misc_cost / wa_keko-losgr.
ENDIF.
CHECK NOT d_mat_cost IS INITIAL.
WRITE:/ d_mat_cost, d_lab_cost, d_over_head, d_ext_purch,d_misc_cost.
ENDLOOP.
GET RUN TIME FIELD ld_endtime.
ld_runtime = ld_endtime - ld_starttime. "time in micro seconds
WRITE:/ ld_runtime, 'micro, nano or what ever seconds'.
ld_runtime = ld_runtime / 1000000. "time in seconds
WRITE:/ ld_runtime, 'seconds'.
ENDFORM. " GET_COST_VALUES
Runtime takes about 302 seconds to execute
What if the internal table was of type SORTED?
So what if you make the internal table SORTED, surely this will also have a positive effect on the performance of nested loops! After all it seems logical that it would.
So what do you think? Will it have a positive effect on the above experiment?? See the SORTED vs. STANDARD internal table comparison example ABAP report for full details and timings.
Related Articles
ABAP Performance Improvements - Example code and information on various performance enhancements
Performance tuning using GROUPBY
Improving the performance of your ABAP internal table processing
Switch on RTA Dynamically within ABAP Code
Using the Runtime Analysis tool, both manually and from with a program
SAP SQL Trace
Improve ABAP performance with the runtime analysis tcode SAT (SE30)'
Compare performance of various ABAP code using runtime analysis tcode SAT '