performance tuning

[former_member199353]

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.

[Former Member]

Try removing FOR ALL ENTRIES.

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

Cheers

VJ

[Former Member]

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.

[Former Member]

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

[Former Member]

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