How to avoid select with in a loop.

[Former Member]

Hi friends,

can any tell how to avoid the select with in a loop for the following code, since it badly effecting my performance. if possible with code

SELECT matnr werks

FROM marc

INTO TABLE it_marc

FOR ALL ENTRIES IN it_ausp_1

WHERE matnr = it_ausp_1-objek AND

werks = p_werks

AND mmsta LE 60.

SORT it_marc BY matnr.

CLEAR wa_ausp_1.

CLEAR wa_tabix.

LOOP AT it_ausp_1 INTO wa_ausp_1.

wa_tabix = sy-tabix.

READ TABLE it_marc INTO wa_marc WITH

KEY matnr = wa_ausp_1-objek

BINARY SEARCH.

IF sy-subrc EQ 0.

ELSE.

DELETE it_ausp_1 INDEX wa_tabix.

ENDIF.

ENDLOOP. " LOOP AT it_ausp_1 INTO wa_ausp_1.

APPEND LINES OF it_ausp_1 TO it_tal.

IF it_tal[] IS NOT INITIAL.

LOOP AT t_plan_order INTO fs_plan_order.

SELECT r~rsnum

r~rspos

r~matnr

r~nomng

r~meins

r~plnum

r~bdter

r~ewahr

r~alprf

r~posnr

r~baugr

m~dispo

INTO TABLE t_resb

FROM resb AS r

INNER JOIN marc AS m

ON r_{baugr = m}matnr

AND r_{werks = m}werks

FOR ALL ENTRIES IN it_tal

WHERE r~rsnum = fs_plan_order-rsnum

AND r~matnr = it_tal-objek

AND r~xloek = ' '

AND r~werks = p_werks.

APPEND LINES OF t_resb TO t_tacl.

ENDLOOP.

[harikrishnan_m]

Hi,

Try this way....

IF it_tal[] IS NOT INITIAL.

SELECT r~rsnum

r~rspos

r~matnr

r~nomng

r~meins

r~plnum

r~bdter

r~ewahr

r~alprf

r~posnr

r~baugr

m~dispo

INTO TABLE t_resb

FROM resb AS r

INNER JOIN marc AS m

ON r_{baugr = m}matnr

AND r_{werks = m}werks

FOR ALL ENTRIES IN it_tal

WHERE r~matnr = it_tal-objek

AND r~xloek = ' '

AND r~werks = p_werks.

IF SY-SUBRC EQ 0.

LOOP AT t_plan_order INTO fs_plan_order.

LOOP AT t_resb INTO fs_resb WHERE

rsnum = fs_plan_order-rsnum.

APPEND fs_resb TO t_tacl.

ENDLOOP.

ENDLOOP.

ENDIF.

Rewards if usefullll

Regards,

ABAPer 007

[Former Member]

i think for all entries will not work because objek and matnr are different data type. for this query what he can do is try to loop and collect all the Objek in ranges and retreive from resb based on that ranges objek and do read inside the loop .

loop at it_tal.

collect all objek in ranges.

endloop.

select resb where matnr in ranges_objek.

loop at it_tal.

read from resb.

endloop.

[Former Member]

Hi,

You can get done with selecting records fron both tables including RESB and then match records from both tables based on rsnum and write only the matching records into a 3rd internal table and use that for further processing.

Example:

select from plan_order into t_plan_order ...

select from resb into t_resb.....

loop at t_plan_order.

read table t_resb with key rsnum = t_plan_order-rsnum.

if sy-subrc = 0.

.

.

endif.

continue..

another suggestion I would give is avoid inner joins. use read table in the above manner instead, will improve performance to a great extent.

Regards,

Mrunal.

[former_member194613]

SELECT r~rsnum r~rspos r~matnr  r~nomng r~meins r~plnum r~bdter r~ewahr r~alprf r~posnr r~baugr m~dispo
          INTO TABLE t_resb
          FROM resb AS r
          INNER JOIN marc AS m
         ON r~baugr = m~matnr
         AND r~werks = m~werks
         FOR ALL ENTRIES IN it_tal
         WHERE r~rsnum = fs_plan_order-rsnum
         AND r~matnr = it_tal-objek
         AND r~xloek = ' '
         AND r~werks = p_werks.

The logic of this select is completely weird. Whcih order of the db-tables should be used, which indexes?

On RESB matnr, werks and xloek are in one index, but rsnum not.

Are the conditions in fs_plan_order and it_tal connected or independent?

It is probably much faster, to leave the fs_plan_order condition away and check it on the result.

SELECT r~rsnum r~rspos r~matnr  r~nomng r~meins r~plnum r~bdter r~ewahr r~alprf r~posnr r~baugr m~dispo
          INTO TABLE t_resb
          FROM resb AS r
          INNER JOIN marc AS m
         ON   m~matnr  = r~baugr 
         AND m~werks =  r~werks 
         FOR ALL ENTRIES IN it_tal
         WHERE         AND r~matnr = it_tal-objek
         AND r~xloek = ' '
         AND r~werks = p_werks.

sort fs_plan_order by rsnum.
loop at it_tal in wa.
   read table fs_plan_order
          with key rsnum = wa-rsnum
          binary search.
   if sy-subrc eq 0.
     append ...
   endif.
endloop.

Siegfried

[Former Member]

Hi,

First retrieve all values into table t_resb except using

'rsnum = fs_plan_order-rsnum ' condition in where clause.

then,

LOOP AT t_plan_order INTO fs_plan_order.

READ TABLE t_resb INTO wa_resb with key rsnum = fs_plan_order-rsnum.

move all field into t_tacl using work areas.

ENDLOOP.

Regards,

kk.