Nested Loop Vs Parallel Cursor


Scenario: Many times In development scenario we come across situations where we need to have a nested loop to find our the matching records in the Header and the Item table. In that case we follow Nested loop which takes a longer processing time. To Improve the program performance the Nested Loop Condition can be optimized in a certain way called Parallel Cursor technique to improve the performance.

The below post demonstrates the same by considering the two table : BKPF & BSEG.


 

REPORT  zparallel_cursor_test.

DATA lt_bkpf TYPE TABLE OF bkpf,

              lt_bseg TYPE TABLE OF bseg,

              ls_bkpf TYPE bkpf,

              ls_bseg TYPE bseg.

DATA lv_time1 TYPE i,

              lv_time2 TYPE i,

              lv_time3 TYPE i,

              lv_time4 TYPE i,

              lv_res_time_gn_process TYPE i,

              lv_res_time_pa_process TYPE i,

              indx TYPE i.

DATA lv_bkpf_recs TYPE i.

DATA lv_bseg_recs TYPE i.

START-OF-SELECTION.

  SELECT FROM bkpf INTO TABLE lt_bkpf.
SELECT FROM bseg INTO TABLE lt_bseg.

  DESCRIBE TABLE lt_bkpf LINES lv_bkpf_recs.

  DESCRIBE TABLE lt_bseg LINES lv_bseg_recs.

  GET RUN TIME FIELD lv_time1.
LOOP AT lt_bkpf INTO ls_bkpf.
LOOP AT lt_bseg INTO ls_bseg WHERE vbeln ls_bkpfbelnr.

* BUSINESS LOGIC GOES HERE.
ENDLOOP.
ENDLOOP.

GET RUN TIME FIELD lv_time2.

lv_res_time_gn_process lv_time2 – lv_time1.

  * Applying Parallel Cursor Technique

 SORT lt_bkpf BY belnr.
SORT lt_bseg BY belnr.


GET RUN TIME FIELD lv_time3.
LOOP AT lt_bkpf INTO ls_bkpf.
READ TABLE lt_bseg INTO ls_bseg WITH  KEY belnr ls_bkpfbelnr BINARY SEARCH.
IF sysubrc 0.
indx sytabix.
LOOP AT lt_bseg INTO ls_bseg FROM indx.
IF ls_bsegbelnr <> ls_bkpfbelnr.
EXIT.
ENDIF.
* BUSINESS LOGIC GOES HERE.
ENDLOOP.
ENDIF.
ENDLOOP.

GET RUN TIME FIELD lv_time4.

  lv_res_time_pa_process lv_time4 – lv_time3.

  WRITE :‘Comparision Between Nested Loop And Parallel Cursor Technique’.
WRITE:‘No Of records in BKPF(Header) Table = ‘lv_bkpf_recs.
WRITE:‘No Of records in BSEG(Item) Table = ‘lv_bseg_recs.

Write :‘Time Taken in Normal Nested loop Processing’lv_res_time_gn_process  ,‘Vs.’ ,
‘ Time Taken In Parallel Cursor Technique’lv_res_time_pa_process.


Output:


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s