topic Re: select inside loop in Application Development and Automation Discussions

select inside loop

Former Member — Mon, 19 Feb 2007 11:47:41 GMT

hello ,

i am new to abap as well as to this forum.pls help me....

i have code something like this..

LOOP AT ITAB.

CLEAR V_KNUMH.

SELECT SINGLE KNUMH FROM A506 INTO V_KNUMH WHERE MATNR = ITAB-MATNR.

SELECT SINGLE KBETR FROM KONP INTO ITAB-KBETR2 WHERE KNUMH = V_KNUMH.

MODIFY ITAB.

CLEAR ITAB.

ENDLOOP.

will this SELECT INSIDE LOOP affects the performance? how to avoid this?

thanx in advance..

regards,

maya

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:50:18 GMT

Welcome to SDN!!!

S it will affect the performance.

Change it like

IF not itab[] is initial.
SELECT knumh from a506
             into table i_a506
             for all entries in itab
             where matnr = itab-matnr.
If sy-subrc = 0.
SELECT matnr KBETR FROM KONP 
             INTO table i_konp
             for all entries in i_a506
             WHERE KNUMH = i_a506-KNUMH.
ENDIF.
ENDIF.
LOOP AT ITAB.
READ TABLE i_konp WITH KEY ........
IF sy-subrc = 0.
itab-knmuh = i_konp-knmuh.
endif.
MODIFY ITAB.
CLEAR ITAB.
ENDLOOP.

Message was edited by:

Judith Jessie Selvi

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:51:15 GMT

hi Maya,

Select Single statement inside a loop will not affect much from performance point of view. .. but tbetter try using it outside the loop .

Regards,

Santosh

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:51:31 GMT

thank u for ur response..

pls let me know how can i avoid this....

regards,

maya.

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:51:46 GMT

It will effect performance

Instead of that first select all the data from A506 and KONP into 2 internal tables and then READ the data in the loop

Re: select inside loop

sreeramkumar_madisetty — Mon, 19 Feb 2007 11:54:50 GMT

Welcome to SDN.

It will defintely affects the Performance.

Try to avoid selects inside the loop for good coding .

Regards,

kumar

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:55:42 GMT

select knumh
         matnr into table it_knumh
         from A506.

select kbetr
          knumh 
          into table it_kbetr.
          from KONP.

LOOP AT ITAB.
CLEAR V_KNUMH.
read table it_knumh with key matnr eq itab-matnr.
v_knumh = it_knumh-knumh.
read table it_kbetr with key knumh eq v_knumh.
ITAB-KBETR2 = it_kbetr-kbetr.

MODIFY ITAB.
CLEAR ITAB.
ENDLOOP.

Message was edited by:

Chandrasekhar Jagarlamudi

Re: select inside loop

former_member404244 — Mon, 19 Feb 2007 11:55:58 GMT

Hi maya,

welcome to SDN

use two internal tables as itab1 and itab2 and then get the data in these tables basing on itab.and then use loop

If not itab is initial.

SELECT SINGLE KNUMH FROM A506 INTO table itab1

FOR ALL ENTRIES IN ITAB WHERE MATNR = ITAB-MATNR.

IF SY-SUBRC EQ 0.

SELECT SINGLE KBETR FROM KONP INTO ITAB2for all entries in itab1 WHERE KNUMH = itab1-KNUMH.

endif.

loop at itab.

read table itab1 with key matnr = itab-matnr.

if sy-subrc eq 0.

move : itab1-kunmh to some variable

read table itab2 with key knumh = itab1-knumh.

if sy-subrc eq 0.

move itab2-kbetr to ....

endif.

endloop.

regards,

nagaraj

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:58:44 GMT

This will definetly affect the performance.

First you have to select the complete data into internal table.

data: begin of t_knumh occurs 0,

matnr type matnr,

knumh type knumh,

end of t_knumh.

data: begin of t_kbetr2 occurs 0,

knumh type knumh,

kbetr2 type kbetr

end of t_kbetr2.

select matnr knumh from a506 into t_knumh for all entries in itab

where matnr = itab-matnr.

select knumh kbetr from konp into t_kbetr2 for all entries in t_knumh

where knumh = t_knumh-knumh.

loop at itab.

read table t_knumh with key matnr = itab-matnr.

if sy-subrc eq 0.

read table t_kbetr2 with key knumh = t_knumh.

if sy-subrc eq 0.

move t_kbetr2-kbetr2 to itab-kbetr2.

modify itab.

endif.

endloop.

Regards,

Aravind

Re: select inside loop

Former Member — Mon, 19 Feb 2007 11:59:12 GMT

Maya,

You can avoid this looping using the below code:

SELECT konp~kbetr INTO CORRESPONDING FIELDS OF table itab FROM konp

inner join A506 ON konp_{knumh = a506}knumh

FOR ALL ENTRIES IN itab

WHERE a506~matnr = itab-matnr.

Thanks

Re: select inside loop

Former Member — Mon, 19 Feb 2007 12:01:12 GMT

Hi,

A Select Statement inside a Loop definitely affects the performance. Instead try to use the following.

SELECT knumh FROM a506 into table itab_a506 FOR ALL ENTRIES IN itab

WHERE matnr = itab-matnr.

SELECT KBETR FROM konp into table itab_konp FOR ALL ENTRIES IN

itab_a506 WHERE knumh = itab_a506-knumh.

Now read the internal tables and modify the data in itab.

Regards,

Sowmya.