topic Query tuning in Application Development and Automation Discussions

Query tuning

Former Member — Thu, 08 Mar 2007 06:50:17 GMT

Hi,

We have the following query in one of our ABAP programs, which is taking a long time to execute.

SELECT

ekko~bsart

ekpo~ebeln

ekpo~ebelp

ekpo~txz01

ekpo~matnr

ekpo~bukrs

ekpo~werks

ekpo~matkl

ekpo~menge

ekpo~netpr

ekpo~peinh

ekpo~brtwr

ekpo~mtart

INTO TABLE fp_i_ekpo

FROM ( ekpo INNER JOIN ekko ON ekpo_{ebeln = ekko}ebeln )

WHERE

ekko~bsart IN s_bsart AND

ekko~ebeln IN s_ebeln AND

ekpo~ebelp IN s_ebelp AND

ekpo~werks IN s_werks AND

ekpo~matnr IN s_matnr AND

ekpo~bukrs = p_bukrs AND

ekpo~vrtkz <> space.

How can we tune this query?

What are the transactions which can give us the performance of a query.

Any help would be greatly appreciated.

Re: Query tuning

Former Member — Thu, 08 Mar 2007 07:22:28 GMT

What are the transaction to check performance of a query?

ans : transaction is ST05.

Switch on the trace in this transcation, then swithc it off to find out how much time was taken to execute the query.

In addition ST05 tells you the query selection path i.e whether any index was used in fetching data. If you can tell us which of the select option have data in it, then it becomes easy to analyze.

Re: Query tuning

Former Member — Thu, 08 Mar 2007 07:35:53 GMT

Try using MASSEKPO view instead of join. It contains most of the fields from EKKO, EKPO join. I think it would be better performance wise.

To test use ST05 on two test program one using join and one using MASSEKPO view

Re: Query tuning

Former Member — Thu, 08 Mar 2007 07:38:47 GMT

Note that MASSEKPO use condition EKKO-BSTYP = 'F', which means it select ONLY purchase orders and not RFQs (A), or contracts (K) or scheduling agreements (L).

Re: Query tuning

Former Member — Thu, 08 Mar 2007 07:39:35 GMT

Hi ,

Avoid using joins where there is large amount of data that has to be retrieved since it takes a toll on performance in these cases. Use for all entries instead.

Moreover see the indexes that have been created and used through ST05.

If there is no index that is created or used you can then create a secondary index to improve performance.

Regards,

Sunmit.

Re: Query tuning

Former Member — Thu, 08 Mar 2007 07:41:25 GMT

Hi Mick,

try using for all entries instead of join.

perform a select on ekko table and then on ekpo.

you can chk the performance using st05.

select ebeln bsart into table it_ekko from ekko

WHERE

bsart IN s_bsart AND

ebeln IN s_ebeln .

if it_ekko is not initial.

sort it_ekko.

select ebeln

ebelp

txz01

matnr

bukrs

werks

matkl

menge

netpr

peinh

brtwr

mtart into table it_ekpo from ekpo

for all entries in it_ekko where ebeln = it_ekko-ebeln and

ebelp IN s_ebelp AND

werks IN s_werks AND

matnr IN s_matnr AND

bukrs = p_bukrs AND

vrtkz <> space.

endif.

regards,

keerthi

Re: Query tuning

Former Member — Thu, 08 Mar 2007 08:02:21 GMT

Mick,

We fine tune in diff. ways.

1. Use for all entries by separating the select statements.(first select data from

EKKO and based on ekko data select from EKPO).

2. Use PACKET SIZE in select statement.

SELECT

ekko~bsart

ekpo~ebeln

ekpo~ebelp

ekpo~txz01

ekpo~matnr

ekpo~bukrs

ekpo~werks

ekpo~matkl

ekpo~menge

ekpo~netpr

ekpo~peinh

ekpo~brtwr

ekpo~mtart

APPENDINIG TABLE fp_i_ekpo

FROM ( ekpo INNER JOIN ekko ON ekpo_{ebeln = ekko}ebeln )

PACKET SIZE 20000

WHERE

ekko~bsart IN s_bsart AND

ekko~ebeln IN s_ebeln AND

ekpo~ebelp IN s_ebelp AND

ekpo~werks IN s_werks AND

ekpo~matnr IN s_matnr AND

ekpo~bukrs = p_bukrs AND

ekpo~vrtkz <> space.

ENDSELECT.

Pls. reward if useful