topic Re: peformance tuning in Application Development and Automation Discussions

peformance tuning

Former Member — Mon, 24 Jul 2006 05:23:28 GMT

hi experts

i have got stuck in performance tuning osf one query

how can i tune this query by breaking it into two parts

SELECT a~vguid

b~vbeln

b~posnr

b~charg

b~bwtar

c~kunnr

b~kwmeng

INTO TABLE i_sales

FROM vlccuorder AS a

INNER JOIN vbap AS b

ON ( a_{vbeln EQ b}vbeln

AND a_{posnr EQ b}posnr )

INNER JOIN vbak AS c

ON ( b_{vbeln EQ c}vbeln )

JOIN lqua AS d

ON ( b_{charg EQ d}charg )

WHERE b~vbeln IN s_vbeln

AND c~kunnr IN s_kunnr

AND d~werks IN s_werks

AND c~auart EQ 'ZOR'

AND b~abgru EQ space.

wherin primary key fields of tables are:

a)vlccuorder is vguid

b)vbap is vbeln and posnr

c)vbak is vbeln

thanks in advance.

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 05:36:12 GMT

You don't seem to be selecting an fields from the LQUA table....So y r u doing a JOIN on LQUA table?!

That apart....I think you shd first do the VBAK n VBAP selects as ur selecting that data based on the selection screen data.

Then based on the data selected u can go in for the 'vlccuorder' select.

-Aarthi.

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 05:42:04 GMT

idea is i want to break two tables- vbap and vbak

by using "for all entries in" or may be "using corresponding fields of"

thanks if u can guide

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 05:44:51 GMT

y u want to break into vbak and bap...

one is item level and othe is header level table ...

it is better u take join on both table and fetch value in an internal table ...

now on the bases of these value u fetch all required values from diffrent table...

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 05:53:58 GMT

actually i think these two tables are very big data tables and join of these is taking most of the execution time.

can u tell how to do?

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 06:04:02 GMT

not rite...

they have big data but there are lots of key fields in these table..

data: begin of i_sales occurs 0,

vbeln

posnr

charg

bwtar

kunnr

kwmeng

vguid

end of i_sales.

select b~vbeln

b~posnr

b~charg

b~bwtar

c~kunnr

b~kwmeng

INTO TABLE i_sales

FROM vbap AS b

INNER JOIN vbak AS c

ON b_{vbeln EQ c}vbeln

inner JOIN lqua AS d

ON b_{charg = d}charg

WHERE b~vbeln IN s_vbeln

AND c~kunnr IN s_kunnr

AND d~werks IN s_werks

AND c~auart EQ 'ZOR'

AND b~abgru EQ space.

now select value vguid for all entries in i_selecs from

vlccuorder

data:begin of itab occurs 0,

vbeln type vbeln,

vguid type vguid,

end of itab.

select vbeln vguid into table itab from vlccuorder

for all entries in i_sales

where vbeln = i_sales-vbeln.

sort itab .

loop at i_sales .

read table itab binary search with key vbeln = i_sales-vbeln

i_sales-vguid = itab-vguid.

modify i_sales.

endloop.

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 06:15:48 GMT

then try and send me the breaked query as the way u are saying.

lets see whether it is tuned or not.

thanks