Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 

Reading data within a loop can result in multiple re-reading of the same data.

In this code we first read data from table BSEG (67.383 records) and in a LOOP we will read BKPF (9.425 records).

REPORT ZHAR1MH002B.

DATA wa_bseg TYPE bseg.
DATA it_bseg LIKE TABLE OF wa_bseg.
data: t0 type i,
      t1 type i,
      t2 type i.

get run time field T1.

SELECT belnr gjahr FROM bseg INTO CORRESPONDING FIELDS OF TABLE it_bseg
                   WHERE gjahr = '2010'
                   order by primary key.

LOOP AT it_bseg INTO wa_bseg.
  SELECT SINGLE belnr FROM bkpf INTO wa_bseg-belnr
                                WHERE gjahr = wa_bseg-gjahr.
ENDLOOP.

get run time field T2.
T0 = T2 - T1.

write: / 'Mean Runtime:', T0,'microseconds'.

In our environment we see a runtime of

Mean Runtime: 163282.895  microseconds

This kind of statement is found using Transaction ST05 (Performance Trace, using SQL Trace).

We will look for value-identical statements. On top of our list we have the SELECT SINGLE access to table BKPF.

How to optimize:

To avoid re-reading the same data from the database (slow) or disk (poor), use buffering.

You do have two possibilities

1. Single record buffering (in case of memory concerns)

2. Table buffering (number of different records is not large)


As a prerequisite the processed data have to sorted by the fields used for reading. In our example we will use a HASHED TABLE to store the data read from BSEG and table buffering for records read from BKPF to do the optimization.

REPORT ZHAR1MH002A.

DATA wa_bseg TYPE bseg.
DATA it_bseg LIKE TABLE OF wa_bseg.
DATA it_bs TYPE HASHED TABLE OF bseg WITH UNIQUE KEY gjahr WITH HEADER LINE.
data: t0 type i,
      t1 type i,
      t2 type i.

get run time field T1.

SELECT belnr gjahr FROM bseg INTO CORRESPONDING FIELDS OF TABLE it_bseg
                   WHERE gjahr = '2010'.

LOOP AT it_bseg INTO wa_bseg.
  CLEAR it_bs.
  READ TABLE it_bs WITH KEY gjahr = wa_bseg-gjahr.
  IF sy-subrc NE 0.
    SELECT SINGLE belnr FROM bkpf INTO wa_bseg-belnr
                        WHERE gjahr = wa_bseg-gjahr.
    it_bs-gjahr = wa_bseg-gjahr.
    it_bs-belnr = wa_bseg-belnr.
    INSERT it_bs INTO TABLE it_bs.
  ELSE.
    wa_bseg-belnr = it_bs-belnr.
  ENDIF.
ENDLOOP.

get run time field T2.
T0 = T2 - T1.

write: / 'Mean Runtime:', T0,'microseconds'.

The improved code has a much better runtime than before

Mean Runtime:    987.628  microseconds