Nested Loops

[Former Member]

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.

endloop.

Thanks & Regards ::

Balaji T

[uwe_schieferstein]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

Rob

[Former Member]

Hi All,

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

Thanks & Regards ::

Balaji T

[Former Member]

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

Rob

[Former Member]

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

Thanks & Regards ::

Balaji T