topic Re: peformance tuning in Application Development and Automation Discussions

peformance tuning

Former Member — Mon, 24 Jul 2006 05:22:41 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:26:13 GMT

Hi Kunal ,

first get the SO from VBAK+VBAP and then for <b>vlccuorder</b>.

Regards

Prabhu

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 05:29:43 GMT

Hi Kunal,

Check whether you have used all the KEY FIELDS for Where Condition you hav eused in your Select Query.

Cheers,

Prashanth

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 05:37:20 GMT

the query fields in where condition are covered,

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 06:59:00 GMT

it will be helpful if u send me some doc. related that can be helpful.

please also tell me how to use index in tuning a query.

thanks

Re: peformance tuning

former_member186741 — Mon, 24 Jul 2006 07:06:46 GMT

Before advising on how to tune it would be helpful to know how many rows will there usually be in the result table?

Will the s_ variables have many entries in them or just a few?

Also how big is the table 'a'? i.e. how many rows in vlccuorder?

Re: peformance tuning

former_member480923 — Mon, 24 Jul 2006 07:09:10 GMT

Hi,

Use The following logic;

Select all the VBELN's and details on join of VBAK and VBAP into table itab. Do For All Entries on that ITAB to get the vguiid from vlccuorder into another table. Move all the entries of both the table to a final table.

Hope this Helps

Anirban

Re: peformance tuning

Former Member — Mon, 24 Jul 2006 07:14:14 GMT

<b>just copy and paste ...</b>

data: begin of i_sales occurs 0,
vbeln like vbap-vbeln,
posnr like vbap-posnr,
charg like vbap-charg,
bwtar  like vbap-bwtar,
kunnr  like vbak-kunnr,
kwmeng like vbap-kwmeng,
vguid  like vlccuorder-vguid,
end of i_sales.

data:begin of itab occurs 0,
vbeln type vbeln,
vguid type vguid,
end of itab. 



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.

if not i_sales[] is initial.
select vbeln vguid into table itab from vlccuorder
for all entries in i_sales
where vbeln = i_sales-vbeln.
endif.
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 07:17:11 GMT

result table, u mean final result internal table will

be having few entries.

table vlccuorder is having around 200 rows

vbap and vbpa tables are having entries around 800 each

thanks

Re: peformance tuning

former_member186741 — Mon, 24 Jul 2006 07:44:00 GMT

with such low numbers I don't think the sql needs breaking up. I think it's just innefficient because the joins to LQUA is not specified at a low enough level AND does not specify the material, only the batch(CHARG) so hitting this table will be very inefficient. I also think it should be restructured to work down the hierarchy from top to bottom.

This might be better:

SELECT vlcc_{vguid vbap}vbeln vbap_{posnr vbap}charg

vbap_{bwtar vbak}kunnr vbap~kwmeng

INTO corresponding fields of TABLE i_sales

FROM

vlccuorder AS vlcc

INNER JOIN vbap

ON ( vlcc_{vbeln EQ vbap}vbeln

AND vlcc_{posnr EQ vbap}posnr )

INNER JOIN vbak

ON ( vbap_{vbeln EQ vbak}vbeln )

JOIN lqua

ON ( vbap_{charg EQ lqua}charg )

WHERE vbap~vbeln IN s_vbeln

AND vbak~kunnr IN s_kunnr

AND lqua~werks IN s_werks

AND vbak~auart EQ 'ZOR'

AND vbap~abgru EQ space.

FROM vbak

JOIN vbap

ON ( vbap_{vbeln EQ vbak}vbeln )

join vlccuorder as vlcc

ON ( vlcc_{vbeln EQ vbap}vbeln

AND vlcc_{posnr EQ vbap}posnr )

JOIN lqua

ON ( lqua_{matnr eq vbap}matnr

and lqua_{werks eq vbap}werks

and lqua_{charg EQ vbap}charg )

WHERE vbak~vbeln IN s_vbeln

AND vbak~kunnr IN s_kunnr

AND vbak~auart EQ 'ZOR'

AND vbap~abgru EQ space.

AND vbap~werks IN s_werks.