topic Re: performance problem in select in Application Development and Automation Discussions

performance problem in select

former_member182371 — Thu, 26 Apr 2007 09:48:03 GMT

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.

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.

i_list_acu-kwmeng = i_list_acu-kwmeng - g_lfimg.

endselect.

endif.

endloop.

Best regards.

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 09:51:56 GMT

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

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 09:53:57 GMT

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

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 09:56:10 GMT

Hi..

U r selecting data inside a loop..which is a very bad logic..

so..use for all entries..

one more thing..

in the loop u r retreiving the fields but not storing them any where...

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

change..the code..

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 09:58:36 GMT

It is very inefficient if u consider the performance.

what you can do:

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.

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 09:59:07 GMT

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

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 10:03:53 GMT

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..

Re: performance problem in select

Former Member — Thu, 26 Apr 2007 10:16:26 GMT

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.