Why a loop of 15k records never ends but debug of each record is quick?

[Former Member]

We've got the following code, but find the running of the program never reaches the Write 'Test' statement after the loop, or in other words, the loop never ends. But if we debug inside the loop for each record, then find the running is pretty quick, but since we've got more 15,000 records in the loop that we can't offord to click the run to cursor of the breakpoint by clicking more 15,000 times to try the whole loop in debug. Any idea to find the reason why this loop never stops?

 lv_tabix LIKE sy-tabix.

Loop AT itab1 into gs_itab1.
          lv_tabix = sy-tabix.

          Select Single MATNR from Z_PO into (gv_MATNR) where f1 = gs_itab1-f1.
          Select Single f2 from Z_tab2 into (gv_f2) where MATNR = gv_MATNR.
          MOVE gv_f2+4(9) TO gs_itab1-f3.

          Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.
          Move gv_MAX_BUDAT(6) to gs_itab1-f4.

          Select  a~f5
            from  Z_PO as a inner join Z_tab2 as b
              on a~EBELN = b~EBELN and a~EBELP = b~EBELP
              into corresponding fields of table itab2
              where b~f1 = gs_itab1-f1 and a~f5 <> '4'
              and a~f <> '5' and b~f6 = 'xxx' and b~f6 > 0.

          SORT itab2 BY f5 DESCENDING.
          READ TABLE itab2 into gs_itab1-f7 index 1.

           sy-tabix = lv_tabix.


          Modify itab1 from gs_itab1 index sy-tabix.

EndLoop.

       Write 'Test'.  "breakpoint can be set here.

Thanks <REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 14, 2008 11:28 AM

[Former Member]

hi,

Check out in this way ... and also try to avoid using select statements inside the loop and endloop statements .....

Loop AT itab1.

move itab1 to gs_itab1.

lv_tabix = sy-tabix.

Select Single MATNR from Z_PO into (gv_MATNR) where f1 = gs_itab1-f1.

Select Single f2 from Z_tab2 into (gv_f2) where MATNR = gv_MATNR.

MOVE gv_f2+4(9) TO gs_itab1-f3.

Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.

Move gv_MAX_BUDAT(6) to gs_itab1-f4.

Select a~f5

from Z_PO as a inner join Z_tab2 as b

on a_{EBELN = b}EBELN and a_{EBELP = b}EBELP

into corresponding fields of table itab2

where b_{f1 = gs_itab1-f1 and a}f5 '4'

and a_{f '5' and b}f6 = 'xxx' and b~f6 > 0.

SORT itab2 BY f5 DESCENDING.

READ TABLE itab2 into gs_itab1-f7 index 1.

sy-tabix = lv_tabix.

Modify itab1 from gs_itab1 index sy-tabix.

EndLoop.

Regards,

Santosh

[Former Member]

hi Santosh,

Changing from:

Loop AT itab1 INTO gs_itab1.

to:

Loop AT itab1.

move itab1 to gs_itab1.

of your suggestion still not working that the loop still can't reach the end.

We understand that we should try to avoid using select inside a loop, but with our senarios, we seem not to have other options. Any idea why the loop never ends?

Thanks!

[rakesh_kumar]

Hi Kevin,

lv_tabix LIKE sy-tabix.
 
Loop AT itab1 into gs_itab1.
*****
         lv_tabix = sy-tabix.          "Break point 1
******
 
          Select Single MATNR from Z_PO into (gv_MATNR) where f1 = gs_itab1-f1.
          Select Single f2 from Z_tab2 into (gv_f2) where MATNR = gv_MATNR.
          MOVE gv_f2+4(9) TO gs_itab1-f3.
 
          Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.
          Move gv_MAX_BUDAT(6) to gs_itab1-f4.
 
          Select  a~f5
            from  Z_PO as a inner join Z_tab2 as b
              on a~EBELN = b~EBELN and a~EBELP = b~EBELP
              into corresponding fields of table itab2
              where b~f1 = gs_itab1-f1 and a~f5  '4'
              and a~f  '5' and b~f6 = 'xxx' and b~f6 > 0.
 
          SORT itab2 BY f5 DESCENDING.
          READ TABLE itab2 into gs_itab1-f7 index 1.
 
           sy-tabix = lv_tabix.
 
 

******
         Modify itab1 from gs_itab1 index sy-tabix.  "Break point 2
********
 
EndLoop.
 
       Write 'Test'.  "breakpoint can be set here.

Put break points at points suggested. Go through the loop two times.

what are the values of variables : lv_tabix, sy-tabix at and after both break points .

Also we need to know the number of lines in the table gs_itab1 after both statements are executed.

Otherwise ,You have two options:

1. use lv_tabix instead of sy-tabix in

         Modify itab1 from gs_itab1 index lv_tabix.

2. Use field symbol <fs_itab1> for looping by using

Loop AT itab1 assigning <fs_itab1>.

Replace all occurences of gs_itab1 by <fs_itab1>

and comment the modify statement as with field symbol the modify statement won't be required.

Regards, Rakesh

Edited by: Rakesh Kumar on Apr 13, 2008 8:33 AM

[Former Member]

hi Rakesh Kumar,

We have looped to 120 records already (not only loop two times as you suggested), and find all values of lv_tabix, sy-tabix are correct for each loop, and itab1 each row values of the 120 rows are correctly populated as well. In other words, through limited record debugging, there is no any problem. But if run to the end, then the program never stops!

Any idea?

Thanks!

[rakesh_kumar]

Dear Kevin,

How long you have tried running the loop? From carefully looking at the program, it looks like select statements are taking time to execute. You won't feel the problem when you are debugging though. If a select takes 0.1 sec then 15000 * 0.1 second = 1500 second and that is around 25 minutes.

I suggest the following now to confirm this behaviour.

change your program like shown below

*Add new variable
data: f1 type i,
        f2 type i,
        f type i.
**************

lv_tabix LIKE sy-tabix.
 
Loop AT itab1 into gs_itab1.
          lv_tabix = sy-tabix.
          
          get RUN TIME FIELD f1.
          Select Single MATNR from Z_PO into (gv_MATNR) where f1 = gs_itab1-f1.
          Select Single f2 from Z_tab2 into (gv_f2) where MATNR = gv_MATNR.
          MOVE gv_f2+4(9) TO gs_itab1-f3.
 
          Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.
          Move gv_MAX_BUDAT(6) to gs_itab1-f4.
 
          Select  a~f5
            from  Z_PO as a inner join Z_tab2 as b
              on a~EBELN = b~EBELN and a~EBELP = b~EBELP
              into corresponding fields of table itab2
              where b~f1 = gs_itab1-f1 and a~f5  '4'
              and a~f  '5' and b~f6 = 'xxx' and b~f6 > 0.
        get run time field f2.
       f = f + f2 - f1.
       if lv_tabix = 100.
            exit.
       endif.  
          SORT itab2 BY f5 DESCENDING.
          READ TABLE itab2 into gs_itab1-f7 index 1.

           sy-tabix = lv_tabix.
 
 
          Modify itab1 from gs_itab1 index sy-tabix.
 
EndLoop.
      write: 'Time taken for selects for 100 loops: ', f.
       Write 'Test'.  "breakpoint can be set here.

With the changes you can know how much time it takes to perform the select statements 100 times. You will get an idea.

If you can also move

        get run time field f2.
       f = f + f2 - f1.
       if sy-tabix = 100.
            exit.
       endif.  

above sy-tabix = lv_tabix to also add the sorting and reading time from itab2.

Regards, Rakesh

Edited by: Rakesh Kumar on Apr 13, 2008 10:24 AM

[Former Member]

Dear Rakesh,

We added get

run time field f2.

f = f + f2 - f1.

if sy-tabix = 100.

exit.

endif.

after the modify and before Endloop, then the counter number to run for 100 records is

"Time taken for selects for 100 loops: 30,364,577"

Could you convert the counter number of 30,364,577 to a how many minutes?

Thanks!

[Former Member]

HI,

put the followin statement after modify(before endloop)

refresh itab2.

Regards,

Nishant

[Former Member]

Hi kevin,

Please following coding . Add a extra field f1 into the internal table itab2 .

Some questions i needed to ask b_{f6 = 'xxx' and b}f6 > 0. Could not understand this what is f6. Sicne you are equating a character string and integer to same field.

if not itab1[] is initial.

select a_{f5 b}f1 from z_po as a inner join z_tab2 as b

on a_{ebeln = b}ebeln and a_{ebelp = b}ebelp

into corresponding fields of table itab2

for all entries in itab1

where b~f1 = itab1-f1

and a~f5 = '4'

and a~f = '5'

and b~f6 = 'xxx'

and b~f6 > 0.

sort itab2 by f5 descending .

endif.

loop at itab1 into gs_itab1.

lv_tabix = sy-tabix.

Select Single MATNR from Z_PO into (gv_MATNR) where f1 = gs_itab1-f1.

Select Single f2 from Z_tab2 into (gv_f2) where MATNR = gv_MATNR.

MOVE gv_f2+4(9) TO gs_itab1-f3.

Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.

Move gv_MAX_BUDAT(6) to gs_itab1-f4.

read table itab2 into gs_itab1-f7 with key f1 = gs_itab1-f1.

sy-tabix = lv_tabix.

modify itab1 from gs_itab1 index sy-tabix.

endloop.

with regards

Brijesh

[Former Member]

hi Brijesh Shetty,

Yeh, our typo error, "b_{f6 = 'xxx' and b}f6 > 0." should be sth like "b_{f6 = 'xxx' and b}f10 > 0."

We don't think your code is right. The reason is that we have to go through each record in itab1 loop to do all the selections.

Thanks anyway!

[rakesh_kumar]

Dear Kevin,

"Time taken for selects for 100 loops: 30,364,577"

means it has taken 30.3 seconds for 100 loop passes.

Meaning for 15000 loops it should take near 30.3 * 15000 / 100 = 4545 seconds = 1.26 hours.

You should try to fetch all the data from DB into sorted tables before start of the loop and then use "read table with key ... binary search" in the loop.

If you need more help, please write back.

Regards, Rakesh

[Former Member]

Dear Rakesh,

Could you show us the example code on how to "fetch all the data from DB into sorted tables before start of the loop and then use "read table with key ... binary search" in the loop."?

Thanks alot in advance!

[rakesh_kumar]

Dear Kevin,

I give the code I prepared from your inputs. There will be a lot of syntax errors but I believe you can get through that.

DATA: lv_tabix LIKE sy-tabix.

Data: lt_zpo1_1 type table of z_po with HEADER LINE.
DATA: lt_itab2 like itab1. " Should be an internal table with same type as itab1 and should have header line
if itab1 is not initial.
  select f1 matnr from z_po into table lt_zpo1_1 
  for all entries in itab1 
  where f1 = itab1-f1.
endif.

if lt_zpo1_1 is not initial.
  select matnr f2 from z_tab2 into table lt_ztab2 
  for all entries in lt_zpo1_1
  where matnr = lt_zpo1_1-matnr.
endif.
  
 
* Do you really need to get it in a loop because none of selection parameter actually use gs_itab1 directly or indirectly
* and in this case, a select outside will get you gv_MAX_BUDAT and you can use it for every gs_itab1
* if it is not the case, please give more data.
 Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.


if itab1 is not initial.
    Select  b~f1 a~f5
      from  Z_PO as a inner join Z_tab2 as b
        on a~EBELN = b~EBELN and a~EBELP = b~EBELP
        into corresponding fields of table lt_itab2
        for all entries of itab1
        where b~f1 = itab1-f1 and a~f5  = '4'
        and a~f = '5' and b~f6 = 'xxx' and b~f6 > 0.
endif.

sort lt_zpo1_1 by f1.
sort lt_ztab2 by matnr.

sort lt_itab2 by f1 f5 DESCENDING.

 
Loop AT itab1 into gs_itab1.
          lv_tabix = sy-tabix.
 
*          Select Single MATNR from Z_PO into (gv_MATNR) where f1 = gs_itab1-f1.
          read table z_po_1 with key matnr = gs_itab1-f1 BINARY SEARCH.
          move z_po_1-matnr to gv_matnr.

*          Select Single f2 from Z_tab2 into (gv_f2) where MATNR = gv_MATNR.
          read table lt_ztab2 with key matnr = gv_matnr BINARY SEARCH.
          
          MOVE lt_ztab2-f2+4(9) TO gs_itab1-f3.
 
*          Select MAX( BUDAT ) from Z_GR into gv_MAX_BUDAT where Z_GR_sth = 'xxx' and Z_GR_QTY > 0.
*         This has been selected outside the loop          
          Move gv_MAX_BUDAT(6) to gs_itab1-f4.
 
*          Select  a~f5
*            from  Z_PO as a inner join Z_tab2 as b
*              on a~EBELN = b~EBELN and a~EBELP = b~EBELP
*              into corresponding fields of table itab2
*              where b~f1 = gs_itab1-f1 and a~f5  = '4'
*              and a~f = '5' and b~f6 = 'xxx' and b~f6 > 0.
 
*          SORT itab2 BY f5 DESCENDING.
*          READ TABLE itab2 into gs_itab1-f7 index 1.
           READ TABLE lt_itab2 with key f1 = gs_itab1-f1 BINARY SEARCH.
           gs_itab1-f7 = lt_itab2-f7.

           sy-tabix = lv_tabix.
 
          Modify itab1 from gs_itab1 index sy-tabix.
 
EndLoop.
 
       Write 'Test'.  "breakpoint can be set here.

Hope it works.

Regards, Rakesh

[Former Member]

Dear Rakesh,

We are really appreciated your help and have modified all the codes by moving all Select statements based on your suggestion out of the loop. Sounds like the running time is still high and the long run now moves to the outside of the loop.

Also Is Not Initial doesn't work for an itab which contains content. Through debugging, we find the cursor can't get into any statement inside the If statement even if itab is not null:

if itab1 is not initial.
   .....through debugging, the cursor can't get into here even if itab1 contains 15,000 records (some columns have contents)
EndIf.

Thanks alot!

[rakesh_kumar]

Dear Kevin,

My mistake.

Use

if itab1[] is not initial.

instead.

Do the same changes for other internal tables.

Then it should work.

If the select takes a lot of time then also

You have to do a DB level optimization. You have to take st05 trace and look at the details.

Sometimes secondary index on database table helps. Sometimes, if the index are existing, dropping and recreating them help.

Regards Rakesh

[peter_ruiz2]

hi Kevin,

try removing this

 sy-tabix = lv_tabix.

and replacing this code

Modify itab1 from gs_itab1 index sy-tabix.

with this

Modify itab1 from gs_itab1 index lv_tabix.

Regards,

Peter