topic Re: performance tuning in Application Development and Automation Discussions

performance tuning

former_member199353 — Wed, 09 Aug 2006 13:43:04 GMT

Hi,

I am having this problem with performance that in a particular SELECT query the earlier programmers has used inner join and for all entries. this is affecting the performance very badly.can you please suggest an alternative so that the performance can be improved.

the code goes as

SELECT vbeln

fkart

kunag

bukrs

fksto

INTO CORRESPONDING FIELDS OF TABLE i_common_usrstat1

FROM vbrk

FOR ALL ENTRIES IN i_billtypes

WHERE ( fkart = i_billtypes-adjfkart_1 OR

fkart = i_billtypes-adjfkart_2 OR

fkart = i_billtypes-adjfkart_3 )

AND fkdat = p_date

AND bukrs IN s_bukrs.

IF sy-subrc = 0.

SELECT b~vbeln

b~posnr

b~netwr

b~mwsbp

c~vbelv

c~posnv

c~waers

INTO CORRESPONDING FIELDS OF TABLE i_common_usrstat2

FROM ( vbrp AS b

INNER JOIN vbfa AS c

ON ( c_{vbeln = b}vbeln

AND c_{posnn = b}posnr

AND c~vbtyp_n = c_invty ) )

FOR ALL ENTRIES IN i_common_usrstat1

WHERE b~mandt EQ sy-mandt

AND b~vbeln EQ i_common_usrstat1-vbeln.

Re: performance tuning

Former Member — Wed, 09 Aug 2006 13:47:20 GMT

Try removing FOR ALL ENTRIES.

FOR ALL ENTRIES is a loop on database. So its better to avoid.

Cheers

Re: performance tuning

Former Member — Wed, 09 Aug 2006 13:52:02 GMT

Hi Anil,

Try avoiding into corresponding fields...

Also dont use this in the where condition.

WHERE b~mandt EQ sy-mandt

hope this helps,

keerthi.

Re: performance tuning

Former Member — Wed, 09 Aug 2006 13:54:44 GMT

Suggestions to improve performance.

1. Use Loop...EndLoop instead of FOR ALL ENTRIES.

Loop at i_billtypes.

select vbeln....in s_bukrs.

endloop.

Same for the next query.

2. Avoid using OR conditions in the WHERE condition.

The best is to populate a range using the i_billtypes-adjfkart_1, adjfkart_2 and adjfkart_3 and use the condition FKART IN R_FKART.

Regards

Anurag

Re: performance tuning

Former Member — Wed, 09 Aug 2006 13:56:14 GMT

Hi anil,

If at all you`re very sure that you`d need data from itab1 for select query condition use the addition <i>for all entries</i> else don`t try that. But this addition improves performance compared to looping through itab1.

In your second query, instead of table VBFA you can select values from vbak. The reason is table vbfa is sales document flow and hence the record size of the table is larger compared to VBAK.

Hope this should help you, if conviced reward points.

Regards