Use a JOIN : How to combine multiple SELECT statements in a JOIN

[former_member15038]

Quite often data areread within a code to process e.g. HEADER and ITEM data. This can be done with two or more SELECT statements in a sequence or in a LOOP ... ENDLOOP with nested SELECTS.

Example:

REPORT ZHAR1MH003B.

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'.

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

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

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

In our environment (BKPF 9.425 records, BSEG 67.383 records), we have a runtime of

Mean Runtime: 59.172.814  microseconds

This kind of code is found in SAP Code Inspector in the test package 'PERFORMANCE CHECKS'.

In the details we see the affected code

We can optimize this statement with a LEFT OUTER JOIN on table BSEG and BKPF

REPORT ZHAR1MH003A.

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 a~belnr a~gjahr FROM bseg AS a LEFT OUTER JOIN bkpf AS b
                       ON a~gjahr = b~gjahr AND a~belnr = b~belnr
                       INTO CORRESPONDING FIELDS OF TABLE it_bseg
                       WHERE a~gjahr = '2010'.

LOOP AT it_bseg INTO wa_bseg.
ENDLOOP.

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

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

What does this change mean for the runtime

Mean Runtime:    461.749  microseconds