topic Re: Nested Loops in Application Development and Automation Discussions

Nested Loops

Former Member — Thu, 16 Apr 2009 17:29:01 GMT

Hi Experts,

Can you pl give me different ways to improve the performance of the following code. Currently it's taking long time. Want to reduce the processing time. Costcenter table contains more than 300000 records.

select * from /bi0/pcostcenter into table it_pcc

where /bic/zmr2krc1 ne ''.

select * from /bi0/pcostcenter into table it_pcc1

where /BIC/ZMR2K_OWR = <Variable>.

sort it_pcc1 by costcenter.

delete adjacent duplicates from it_pcc1 comparing costcenter.

sort it_pcc.

loop at it_pcc1.

loop at it_pcc where

( /bic/zmr2krc1 = it_pcc1-costcenter or

/bic/zmr2krc2 = it_pcc1-costcenter or

/bic/zmr2krc3 = it_pcc1-costcenter or

/bic/zmr2krc4 = it_pcc1-costcenter or

/bic/zmr2krc5 = it_pcc1-costcenter or

/bic/zmr2krc6 = it_pcc1-costcenter or

/bic/zmr2krc7 = it_pcc1-costcenter or

/bic/zmr2krc8 = it_pcc1-costcenter or

/bic/zmr2krc9 = it_pcc1-costcenter or

/bic/zmr2krc10 = it_pcc1-costcenter or

/bic/zmr2krc11 = it_pcc1-costcenter or

/bic/zmr2krc12 = it_pcc1-costcenter or

/bic/zmr2krc13 = it_pcc1-costcenter or

/bic/zmr2krc14 = it_pcc1-costcenter or

/bic/zmr2krc15 = it_pcc1-costcenter ).

it_pcc2-costcenter = it_pcc-costcenter.

it_pcc2-/bic/zmr2k_owr = it_pcc1-/bic/zmr2k_owr.

append it_pcc2.

clear it_pcc2.

endloop.

Thanks & Regards ::

Balaji T

Re: Nested Loops

uwe_schieferstein — Thu, 16 Apr 2009 17:45:30 GMT

Hello

Why don't you combine the two SELECT statements? Without understanding what you intend to achieve the following coding might be close to what you want:


SELECT COSTCENTER /BIC/ZMR2K_OWR FROM /bi0/pcostcenter 
  INTO CORRESPONDING FIELDS OF TABLE lt_pcc2
  WHERE ( /bic/zmr2krc1 NE '' ) 
  AND ( /BIC/ZMR2K_OWR = <Variable> ).

Regards

Uwe

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 17:54:08 GMT

Hi Uwe,

Following is my requirement.

First SQL statement fetches first set of records into it_pcc based on given condition.

SELECT COSTCENTER /BIC/ZMR2K_OWR FROM /bi0/pcostcenter

INTO CORRESPONDING FIELDS OF TABLE lt_pcc2

WHERE ( /bic/zmr2krc1 NE '' )

Second SQL statement fetches second set of records into it_pcc1 based on given condition.

SELECT COSTCENTER /BIC/ZMR2K_OWR FROM /bi0/pcostcenter

INTO CORRESPONDING FIELDS OF TABLE lt_pcc2

WHERE /BIC/ZMR2K_OWR = <Variable>.

After Fetching 2 sets, I need to do following processing.

For each record in 2nd Set of records I need to find all the matching records of the 1st Set of records which satisfies the following condition. So your suggestion will not work in my case.

loop at it_pcc where

( /bic/zmr2krc1 = it_pcc1-costcenter or

/bic/zmr2krc2 = it_pcc1-costcenter or

/bic/zmr2krc3 = it_pcc1-costcenter or

/bic/zmr2krc4 = it_pcc1-costcenter or

/bic/zmr2krc5 = it_pcc1-costcenter or

/bic/zmr2krc6 = it_pcc1-costcenter or

/bic/zmr2krc7 = it_pcc1-costcenter or

/bic/zmr2krc8 = it_pcc1-costcenter or

/bic/zmr2krc9 = it_pcc1-costcenter or

/bic/zmr2krc10 = it_pcc1-costcenter or

/bic/zmr2krc11 = it_pcc1-costcenter or

/bic/zmr2krc12 = it_pcc1-costcenter or

/bic/zmr2krc13 = it_pcc1-costcenter or

/bic/zmr2krc14 = it_pcc1-costcenter or

/bic/zmr2krc15 = it_pcc1-costcenter ).

Thanks & Regards ::

Balaji T

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 18:01:35 GMT

Hi ,



SELECT COSTCENTER /BIC/ZMR2K_OWR FROM /bi0/pcostcenter 
  INTO CORRESPONDING FIELDS OF TABLE lt_pcc2
 WHERE  /BIC/ZMR2K_OWR = <Variable>.

SELECT COSTCENTER /BIC/ZMR2K_OWR FROM /bi0/pcostcenter 
INTO CORRESPONDING FIELDS OF TABLE lt_pcc1
for all entries in IT_PCC1
WHERE  /bic/zmr2krc1 NE '' 
and   ( /bic/zmr2krc1 = it_pcc1-costcenter or
          /bic/zmr2krc2 = it_pcc1-costcenter or
         /bic/zmr2krc3 = it_pcc1-costcenter or
         /bic/zmr2krc4 = it_pcc1-costcenter or
         /bic/zmr2krc5 = it_pcc1-costcenter or
         /bic/zmr2krc6 = it_pcc1-costcenter or
         /bic/zmr2krc7 = it_pcc1-costcenter or
         /bic/zmr2krc8 = it_pcc1-costcenter or
         /bic/zmr2krc9 = it_pcc1-costcenter or
        /bic/zmr2krc10 = it_pcc1-costcenter or
        /bic/zmr2krc11 = it_pcc1-costcenter or
        /bic/zmr2krc12 = it_pcc1-costcenter or
        /bic/zmr2krc13 = it_pcc1-costcenter or
        /bic/zmr2krc14 = it_pcc1-costcenter or
         /bic/zmr2krc15 = it_pcc1-costcenter ).

sort it_pcc1 by costcenter.
delete adjacent duplicates from it_pcc1 comparing costcenter.

sort it_pcc.
loop at it_pcc1.
loop at it_pcc .
it_pcc2-/bic/zmr2k_owr = it_pcc1-/bic/zmr2k_owr.
append it_pcc2.
clear it_pcc2.
endloop.
endloop.

Regards,

Prabhudas

Edited by: Prabhu Das on Apr 16, 2009 11:32 PM

Edited by: Prabhu Das on Apr 16, 2009 11:34 PM

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 18:02:01 GMT

Not very elegant, but it should work:

SORT it_pcc1 BY costcenter.
LOOP AT it_pcc.
  READ TABLE it_pcc1
    WITH KEY costcenter = it_pcc-/bic/zmr2krc1
    BINARY SEARCH.
  IF sy-subrc NE 0.
    READ TABLE it_pcc1
      WITH KEY costcenter = it_pcc-/bic/zmr2krc1
      BINARY SEARCH.
    IF sy-subrc NE 0.
*      ... and so on for all 15 entries.
    ENDIF.
  ENDIF.
ENDLOOP.

Rob

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 18:59:09 GMT

Hi Rob,

Thank you for the idea of looping on first set of records and reading 2nd set of records. That worked. But I changed the code little bit. Following is the code. I'm awarding points to you.

l_ctr = 0.
  DO 15 TIMES.
     l_ctr = l_ctr + 1.
     l_cnt = l_ctr.
     CONCATENATE 'it_pcc-/bic/zmr2krc' l_cnt into l_field.
     ASSIGN COMPONENT l_field OF STRUCTURE it_pcc TO <fld_name>.
     ASSIGN (l_field) to <fld_name>.
     READ TABLE it_pcc1
          WITH KEY costcenter = <fld_name>
          BINARY SEARCH.
     IF sy-subrc EQ 0.
        it_pcc2-costcenter = it_pcc-costcenter.
        it_pcc2-/bic/zmr2k_owr =  it_pcc-/bic/zmr2k_owr.
        append it_pcc2.
        clear it_pcc2.
        exit.
     ENDIF.
  ENDDO.

Thanks & Regards ::

Balaji T

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 19:20:53 GMT

Glad it helped, but don't forget - the others helped as well.

Rob

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 19:54:08 GMT

Hi All,

Thanks to all of you who helped me in resolving the issue.

Thanks & Regards ::

Balaji T

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 20:32:36 GMT

Balaji - what I really meant that the way to express thanks in the forum is by assigning po(i)nts to answers

Rob

Re: Nested Loops

Former Member — Thu, 16 Apr 2009 22:22:53 GMT

Yes Rob. I got that. Just missed that part last time. Thanks for reminding that.

Thanks & Regards ::

Balaji T