ABAP Parallel cursor to improve Performance of your SAP reports
As we have seen when using a parallel cursor within nested loop functionality it
can significantly improve the processing performance. But what if the internal table was a SORTED itab instead
of a STANDARD one? Will it make any difference?
Well the answer is not a simple one as it all depends on how you setup the sorted table. At first I thought the answer was no as I added the following code to setup a sorted table and ran the program again.
  DATA: it_keph TYPE SORTED TABLE OF keph WITH UNIQUE KEY table_line.
Using the same example as before the SORTED version then took 293 seconds which is a similar time to the STANDARD table at 302. So that was that creating a sorted itab makes little difference. But that wasn’t the full story, after a bit of help and investigation(see comments below) its was pointed out that the problem could be that above example uses the full table key for sorting purposes but only a subset of that within the loop (i.e. kalnr, kalka, bwvar & kadky).
SET Specific key for type SORTED table
So what if we change the SORTED table key so that it matches that used by the LOOP WHERE clause using the following ABAP code.
    DATA: it_keph TYPE SORTED TABLE OF keph WITH UNIQUE KEY kalnr kalka bwvar kadky.
The obvious problem with this is that it would not allow multiple table entries with the same values in the key fields. This would be a major issue for this example, and would infact cause a short dump during the SELECT statement, as there are many entries with the same key value.
NON-UNIQUE KEY
So the solution to this would be to create key that allows multiple entries of the same key (i.e. none unique key) using the follow ABAP code.
    DATA: it_keph TYPE SORTED TABLE OF keph WITH NON-UNIQUE KEY kalnr kalka bwvar kadky.
So how does the optimised SORTED table compare to the parallel cursor
Once you have optimised your SORTED table it actually performs just as well as the parallel cursor, which is actually a bit of a light bulb moment for me. I have never really had much time for sorted tables as they never really seemed to offer anything you couldn’t achieve with a STANDARD table but had a few limitations when adding data. You are also not able to re-sort them to meet your needs during runtime.
This is still true in this case as using a parallel cursor still offers the same performance level in this example but in many situations where you don’t need the freedom standard tables offer, a SORTED table could actually be a better/easier option to go for.
Anyway here is the ABAP code listing for the SORTED itab example.
*&---------------------------------------------------------------------*
*& Report ZREP_PARALLEL_CURSOR
*&---------------------------------------------------------------------*
*&
*& SAP ABAP functionality
*& **********************
*& Select data from database table into internal table
*& Create SORTED internal table
*& Loop statement
*& Write statement to display output report
*& Calculate runtime of an ABAP report using GET RUN TIME FIELD
*& Convert GET RUN TIME FIELD to seconds
*&---------------------------------------------------------------------*
REPORT zrep_parallel_cursor.
DATA: it_keko TYPE STANDARD TABLE OF keko,
wa_keko LIKE LINE OF it_keko,
* it_keph TYPE SORTED TABLE OF keph WITH UNIQUE KEY table_line,
it_keph TYPE SORTED TABLE OF keph WITH NON-UNIQUE KEY kalnr
kalka
bwvar
kadky,
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.
* 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
As you can see this code also takes under 1 second to execute like the parellel cursor version
Result
Same code but using SORTED table instead of STANDARD makes little difference
to the runtime if you don't set it up correctly.
But if done correctly a SORTED table can perform just a fast as using a parallel cursor version,
without having to add any extra code/processing to the LOOP WHERE statement.
Related Articles
Performance tuning using GROUPBY
Improving the performance of your ABAP internal table processing
ABAP Parallel cursor to improve Performance of your SAP reports
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 '
