performance problem in select

[former_member182371]

Hi,

could someone please give me a hint about why this sentence is causing performance problems?

sort i_bote by matnr idnrk vbeln posnr werks clabs.

loop at i_bote.

if i_bote-WERKS_PED = i_bote-WERKS.

i_list_acu-vbeln = i_bote-vbeln.

i_list_acu-posnr = i_bote-posnr.

<b> select t1_{kwmeng sum( t3}lfimg )

from vbap as t1 join vbuk as t2

on t1_{vbeln = t2}vbeln

join lips as t3

on t1_{vbeln = t3}vgbel and

t1_{posnr = t3}vgpos

into (i_list_acu-kwmeng, g_lfimg)

where t1~vbeln = i_list_acu-vbeln

and t1~posnr = i_list_acu-posnr

and t2~wbstk <> 'C'

group by t1~kwmeng.</b>

i_list_acu-kwmeng = i_list_acu-kwmeng - g_lfimg.

endselect.

endif.

endloop.

Best regards.

[Former Member]

hi,

1. Remove Joins and use for all entries statement and use select into table than using select ...end select statment ..

2. Never use select inside loop ... endloop.

Regards,

Santosh

Message was edited by:

Santosh Kumar Patha

[Former Member]

Hi,

Dont use Select..End select in side the loops, make use of READ Statements and Use FOR ALL ENTRIES.

Regards,

Suresh

Message was edited by:

Suresh

[Former Member]

Hi..

U r <b>selecting data inside a loop</b>..which is a very bad logic..

so..use for all entries..

one more thing..

in the loop u r retreiving the fields but <b>not storing them</b> any where...

ur output values are always works for the last itab loop.

change..the code..

[Former Member]

It is very inefficient if u consider the performance.

<b>what you can do:</b>

1.

loop at i_bote.

if i_bote-WERKS_PED = i_bote-WERKS.

endif.

endloop.

without having loop endloop and chicking condition use read internal table with condition.

2. Use select into internal table in case od select...endselect.

3. Instead of join, use for all entries.

4. In condition of select give the condition in such a order that a existing index could be selected by the optimiser.

[Former Member]

Hi

1. Avoid select inside loop.

2. avoid <> in the WHERE clause. delete it after fetching the data.

this will neglect the index scan.

3. Avoid Group By clause.

4. Split the Join on the tables vbap, vbuk and lips.

5. Avoid select-endselect.

regards,

madhu

[Former Member]

pablo,

loop at i_bote.

if i_bote-WERKS_PED = i_bote-WERKS

i_list_acu-vbeln = i_bote-vbeln.

i_list_acu-posnr = i_bote-posnr.

APPEND i_list_acu.

CLEAR i_list_acu.

Endif.

Endloop.

2. Use forall entries of " i_list_acu" into below select statement.

select t1_{kwmeng sum( t3}lfimg )

from vbap as t1 join vbuk as t2

on t1_{vbeln = t2}vbeln

join lips as t3

on t1_{vbeln = t3}vgbel and

t1_{posnr = t3}vgpos

into (i_list_acu-kwmeng, g_lfimg)

where t1~vbeln = i_list_acu-vbeln

and t1~posnr = i_list_acu-posnr

and t2~wbstk <> 'C'

group by t1~kwmeng.

Don't forget to reward if useful..

[Former Member]

hi can you make clear what is the exact performance problem you are getting.Check whether the table is itself a sorted table first.The sort process is only stable if you stable addition.The number of sort fields is also restricted to 250. The selected statement you are using may also create a performance problem. So please post the exact issue.