topic Re: SQL - Running slow.. in Application Development and Automation Discussions

SQL - Running slow..

Former Member — Tue, 18 Oct 2005 09:19:19 GMT

Hi,

I've some performance problem in our production system.

It seems that the table EKPO takes long time...

I've tried some index stuff but with no luck.

Any pointers ?

select x_ekpo~ebeln

x_ekpo~lgort

x_ekpo~matnr

from ekpo as x_ekpo

inner join ekko as x_ekko on x_ekko_{ebeln = x_ekpo}ebeln

into corresponding fields of table i_ekpo_forbidden

for all entries in i_zpo_filter

where

(where_clause)

AND x_ekko~lifnr = i_zpo_filter-lifnr

AND x_ekpo~werks = v_werks

AND x_ekpo~bstyp = 'F' " Purchasing doc.

AND x_ekpo~loekz = space " Deletion indicator

AND x_ekpo~elikz = space. " Delivery Completed indicator

//Martin

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 09:23:10 GMT

Just Change it like this -

select x_ekpo~ebeln

x_ekpo~lgort

x_ekpo~matnr

from ekko as x_ekko

inner join ekpo as x_ekpo on x_ekko_{ebeln = x_ekpo}ebeln

into corresponding fields of table i_ekpo_forbidden

for all entries in i_zpo_filter

where

(where_clause)

AND x_ekko~lifnr = i_zpo_filter-lifnr

AND x_ekpo~werks = v_werks

AND x_ekpo~bstyp = 'F' " Purchasing doc.

AND x_ekpo~loekz = space " Deletion indicator

AND x_ekpo~elikz = space. " Delivery Completed indicator

Cheers.

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 09:29:37 GMT

hi,

your query seems to be ok.

create a index for Lifnr and try with that.

there is no pointers in the sql query. how many records you are retrieving ?

and include some date range ( if possible ) and before the statement use

delete adjacent duplicates from table i_zpo_filter by comapring lifnr.

cheers,

sasi

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 09:38:29 GMT

Hi,

here is some points that slow down the query

- 'for all entries in' has the same effect as

LOOP @ ITAB. SELECT... ENDLOOP.

so it is not performent at all if the table izpo_filter is big.

- dynamic where clause is not performant neither.

if i did give you some usefull info let me know by rewarding me.

regards,

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 09:44:08 GMT

Try to avoid using 'corresponding fields of '.

declare the struture of your internal table such that you can avoid using 'corresponding fields of ' clause in select statement.

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 10:09:20 GMT

Check also that i_zpo_filter has entries, because if it has no entries it will do a select of the whole table without filter and it will be very slow....

check not i_zpo_filter[] is initial.

Regards,

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 14:12:29 GMT

Have you done a performance trace to see which index is being used?

Rob

Re: SQL - Running slow..

Former Member — Tue, 18 Oct 2005 15:18:22 GMT

Try using views like MASSEKPO.

YOu can look for other views that might satisfy your conditions. Use where used from SE11 for table EKPO, select 'Views' from the options.

Choose something that fits your requirement.

Look at view WB2_V_EKKO_EKPO2, I ran a comparison between your select and selecting from this view. The view takes just half the time. Beware some fields are named with _I extension.

Like MATNR is MATNR_I.

I did not check the join conditions in the view. Pls. check that too.

Message was edited by: Cyril Alex