10-18-2005 10:19 AM
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_ekkoebeln = x_ekpoebeln
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
10-18-2005 10:23 AM
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_ekkoebeln = x_ekpoebeln
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.
10-18-2005 10:29 AM
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
10-18-2005 10:38 AM
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,
10-18-2005 10:44 AM
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.
10-18-2005 11:09 AM
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,
10-18-2005 3:12 PM
Have you done a performance trace to see which index is being used?
Rob
10-18-2005 4:18 PM
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