topic Re: Avoiding Nested Loops in Application Development and Automation Discussions

Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 11:50:37 GMT

Hi all,

I want to populate a internal table with Header and Item Level Details. BKPF is the Header Table and BSEG is the Item Details table. Can this be done without using nested LOOPS?

Consider the Logic I am using.


  SELECT bukrs
         belnr
         gjahr
         budat
         xblnr
         bktxt
         FROM   bkpf
         INTO TABLE t_bkpf
         WHERE      bukrs   IN p_bukrs " = p_bukrs
             AND    gjahr   IN p_gjahr  "= p_gjahr
             AND    monat   IN p_monat  " = p_monat
             AND    bstat    = space.


  IF sy-subrc IS INITIAL.

    SORT t_bkpf BY bukrs belnr gjahr.

    SELECT  bukrs
            belnr
            buzei
            gjahr
            hkont
            zlsch
            shkzg
            dmbtr
            FROM bseg
            INTO TABLE t_bseg
            FOR ALL ENTRIES IN t_bkpf
            WHERE     bukrs = t_bkpf-bukrs
               AND    gjahr = t_bkpf-gjahr
               AND    belnr = t_bkpf-belnr
               AND    hkont = w_hkont .

    IF sy-subrc IS INITIAL.

      SORT t_bseg BY  bukrs belnr gjahr buzei.
    ENDIF.

  ENDIF.


LOOP AT t_bkpf INTO wa_bkpf.
  LOOP AT t_bkpf INTO wa_bkpf WHERE bukrs = wa_bkpf-bukrs
                              AND   belnr = wa_bkpf-belnr
                              AND   gjahr = wa_bkpf-gjahr.
      wa_final-bukrs    =  wa_bkpf-bukrs.
      wa_final-budat    =  wa_bkpf-budat.
      wa_final-belnr    =  wa_bkpf-belnr.
      wa_final-shkzg    =  wa_bseg-shkzg.
      wa_final-dmbtr    =  wa_bseg-dmbtr.
      wa_final-xblnr    =  wa_bkpf-xblnr.
      wa_final-bktxt    =  wa_bkpf-bktxt.
      wa_final-hkont    =  wa_bseg-hkont.
      wa_final-zlsch    =  wa_bseg-zlsch.

   ENDLOOP.
ENDLOOP.

Thanks in advance.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 11:53:07 GMT

Hi Arun,

you can use a read statement instead of the inner loop.

LOOP AT t_bseg INTO wa_bseg.

read table t_bkpf INTO wa_bkpf With key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

endif.

ENDLOOP.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 11:54:49 GMT

LOOP AT t_bseg INTO wa_bseg.

clear t_bkpf.

read table t_bkpf INTO wa_bkpf

WHERE bukrs = wa_bseg-bukrs

AND belnr = wa_bseg-belnr

AND gjahr = wa_bseg-gjahr binary search.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

ENDIF.

ENDLOOP.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 11:54:52 GMT

SORT t_bseg BY bukrs belnr gjahr buzei.

LOOP AT t_bkpf INTO wa_bkpf.

read table t_bkpf INTO wa_bkpf binary search with key bukrs = wa_bkpf-bukrs

belnr = wa_bkpf-belnr

gjahr = wa_bkpf-gjahr.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final to itab_final.

ENDLOOP.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 11:55:10 GMT

Hi Arun,

1. I think u have not at all used NESTED Loops.

2. Both SELECTS are independent , and not nested.

3. In fact this is the best way of doing it.

(using FOR ALL ENTRIES )

4. Nested means,

SELECT

select.. endselect.

ENDSELECT.

regards,

amit m.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 11:59:34 GMT

Hi Ravi,Sravanthi, Kishan,

Thanks for your prompt replies.

I forgot to mention that for every record in BKPF(Header) table there are multiple entries in BSEG(Item) Table.

So LOOP and READ table cannot be used.

Regards,

Arun Sambargi

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:03:42 GMT

Hello Amit,

Thanks for you prompt reply.

I forgot to mention that for every record in BKPF(Header) table there are multiple entries in BSEG(Item) Table.

I want to achieve this without using nested LOOPS.



LOOP AT t_bkpf INTO wa_bkpf.
  LOOP AT t_bkpf INTO wa_bkpf WHERE bukrs = wa_bkpf-bukrs
                              AND   belnr = wa_bkpf-belnr
                              AND   gjahr = wa_bkpf-gjahr.
      wa_final-bukrs    =  wa_bkpf-bukrs.
      wa_final-budat    =  wa_bkpf-budat.
      wa_final-belnr    =  wa_bkpf-belnr.
      wa_final-shkzg    =  wa_bseg-shkzg.
      wa_final-dmbtr    =  wa_bseg-dmbtr.
      wa_final-xblnr    =  wa_bkpf-xblnr.
      wa_final-bktxt    =  wa_bkpf-bktxt.
      wa_final-hkont    =  wa_bseg-hkont.
      wa_final-zlsch    =  wa_bseg-zlsch.
 
   ENDLOOP.
ENDLOOP.

Regards,

Arun

Message was edited by: Arun Sambargi

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:06:02 GMT

Hai Arun try with the following code

SELECT

bukrs

belnr

gjahr

budat

xblnr

bktxt

FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN p_bukrs " = p_bukrs

AND gjahr IN p_gjahr "= p_gjahr

AND monat IN p_monat " = p_monat

AND bstat = space.

IF sy-subrc IS INITIAL.

SORT t_bkpf BY bukrs belnr gjahr.

endif.

if not t_bkpf[] is initial.

SELECT

bukrs

belnr

buzei

gjahr

hkont

zlsch

shkzg

dmbtr

FROM bseg

INTO TABLE t_bseg

FOR ALL ENTRIES IN t_bkpf

WHERE bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

AND hkont = w_hkont .

IF sy-subrc IS INITIAL.

SORT t_bseg BY bukrs belnr gjahr buzei.

ENDIF.

loop at t_bkpf.

read table it_bseg with key bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

binary search.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

endif.

endloop.

Thanks & regards

Sreenivasulu P

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:06:03 GMT

Hi Arun,

Even in that case,

the thumb rule is that you have to loop around the bigger table and read the smaller one.

loop at it_bseg into wa_bseg.

read it_bkpf into wa_bkpf with key ....

if sy-subrc = 0.

*move to final table.

endif.

endloop.

The final table will have no records that are missed out.

Regards,

Ravi

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:09:06 GMT

SORT t_bseg BY bukrs belnr gjahr buzei.

LOOP AT t_bseg INTO wa_bseg.

read table t_bkpf INTO wa_bkpf binary search with key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final to itab_final.

ENDLOOP.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:10:05 GMT

HI,

You can loop at the item details (BSEG) Table and then read the header table (BKPF).

BKPF will have only a single entry for BSEG becoz it is a header record.

LOOP AT t_bseg INTO wa_bseg.

<b>read table t_bkpf with key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.</b> wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

ENDLOOP.

Thanks,

Rashmi.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:16:56 GMT

Hi.

This code is quite efficient. It finds the first entry via binary search, so its fast. And dont let the nested loop fool you. The loop will start with a hit (courtesy of the sy-tabix from the binary search). It will discontinue the search after last entry has been found, since everything is sorted. 'Hope this helps.

SORT t_bseg BY bukrs gjahr belnr.

LOOP AT t_bkpf.

*  find first entry (get index)
   READ TABLE t_bseg 
        WITH KEY bukrs = t_bkpf-bukrs
                 gjahr = t_bkpf-gjahr
                 belnr = t_bkpf-belnr
             BINARY SEARCH. 

   IF sy-subrc EQ 0.

*     get all entries. since they are adjacent,
*     exit when not equal to document key
      LOOP AT t_bseg FROM sy-tabix.
         IF t_bseg-bukrs = t_bkpf-bukrs AND
            t_bseg-gjahr = t_bkpf-gjahr AND
            t_bseg-belnr = t_bkpf-belnr.

            " Do something with t_bseg
         ELSE.
            EXIT.
         ENDIF.
      ENDLOOP.

   ENDIF.

ENDLOOP.

Message was edited by: Emir Cruz

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:17:42 GMT

hi Arun,

Is it helpfull to solve your problem!!!!

SELECT

bukrs

belnr

gjahr

budat

xblnr

bktxt

FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN p_bukrs " = p_bukrs

AND gjahr IN p_gjahr "= p_gjahr

AND monat IN p_monat " = p_monat

AND bstat = space.

IF sy-subrc IS INITIAL.

SORT t_bkpf BY bukrs belnr gjahr.

endif.

if not t_bkpf[] is initial.

SELECT

bukrs

belnr

buzei

gjahr

hkont

zlsch

shkzg

dmbtr

FROM bseg

INTO TABLE t_bseg

FOR ALL ENTRIES IN t_bkpf

WHERE bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

AND hkont = w_hkont .

IF sy-subrc IS INITIAL.

SORT t_bseg BY bukrs belnr gjahr buzei.

ENDIF.

loop at t_bkpf.

read table it_bseg with key bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

binary search.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final.

endif.

endloop.

Thanks & Regards,

Sarera

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:18:55 GMT

repost. see previous post.

Message was edited by: Emir Cruz

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:31:27 GMT

Hi Emir,

I want to avoid nested LOOPs, because it has performance problems.

Regards,

Arun

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:32:32 GMT

check my post again.. i added comments...

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:36:02 GMT

Hi Arun,

This code will surely work.

I don't understand why you are not using it.

LOOP AT t_bseg INTO wa_bseg.

read table t_bkpf INTO wa_bkpf WITH key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final into it_final.

clear wa_final.

endif.

ENDLOOP.

REgards,

Ravi

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:37:00 GMT

Hi Srinivas,

This can be used, but READ statement selects only a single entry so cant be used to select BSEG records, since there are multiple records. Regarding your doubt of the usefulness to sole the problem, its that i want to improve the performance of the code. Since Nested LOOPs consume a lot of Database time.

Regards,

Arun.

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:43:43 GMT

Hi,

If u have perfomance issue with BKPF and BSEG tables , go for the alternate tables based on ur requirement BSIS,BSAS,BSAD,BSID,BSIK,BSAK,BSIP,BVOR..

Regards,

Varma

Re: Avoiding Nested Loops

Former Member — Tue, 11 Apr 2006 12:47:09 GMT

Hi Ravi,

Read statement will select only one record from BSEG, i want to select multiple records at each loop pass of BKPF.

Hope u understand my problem.

Regards,

Arun