topic Re: performance tuning problem in Application Development and Automation Discussions

performance tuning problem

Former Member — Mon, 23 Mar 2009 11:01:36 GMT

Hi,

I have a select query as shown below. here i am facing the performance issue with the this query. can any one plz suggest the better way to optimize the performance of this query....?

SELECT ekko_{ebeln ekko}loekz ekko_{lifnr ekko}ekorg ekko~ekgrp

ekpo_{werks ekpo}pstyp ekpo_{loekz ekpo}elikz ekpo~menge

ekpo_{matnr eket}ebeln eket_{ebelp eket}etenr eket~rsnum

eket~eindt INTO

(ekko-ebeln, ekko-loekz, ekko-lifnr, ekko-ekorg, ekko-ekgrp,

ekpo-werks, ekpo-pstyp, ekpo-loekz, ekpo-elikz, ekpo-menge,

ekpo-matnr, eket-ebeln, eket-ebelp, eket-etenr, eket-rsnum,

eket-eindt)

FROM eket INNER JOIN ekpo

ON ( eket_{ebeln = ekpo}ebeln AND

eket_{ebelp = ekpo}ebelp )

INNER JOIN ekko

ON ( ekpo_{ebeln = ekko}ebeln )

WHERE ekko~ebeln IN ebeln

AND ekko~loekz EQ ' '

AND ekko~lifnr IN lifnr

AND ekko~ekorg IN ekorg

AND ekko~ekgrp IN ekgrp

AND ekpo~werks IN werks

AND ekpo~pstyp EQ '3'

AND ekpo~loekz EQ space

AND ekpo~elikz EQ space

AND ekpo~menge NE 0

AND eket~rsnum NE space.

some statements...

....

endselect.

Re: performance tuning problem

Former Member — Mon, 23 Mar 2009 11:18:00 GMT


WHERE ekko~ebeln IN ebeln
AND ekko~loekz EQ ' '
AND ekko~lifnr IN lifnr
AND ekko~ekorg IN ekorg
AND ekko~ekgrp IN ekgrp
AND ekpo~werks IN werks
AND ekpo~pstyp EQ '3'
AND ekpo~loekz EQ space
AND ekpo~elikz EQ space
AND ekpo~menge NE 0
AND eket~rsnum NE space.

Please add which IN clauses are actually filled !!!!

Re: performance tuning problem

Former Member — Mon, 23 Mar 2009 11:20:58 GMT

SELECT.....ENDSELECT Degrades the preformance.It increases the database hits.

So more number of hits degrades performance.

Just use array operation...(i.e INTO TABLE)

SELECT <FIELDS> FORM <TABLE> INTO TABLE <ITAB>.

Regards,

Gurpreet

Re: performance tuning problem

Former Member — Mon, 23 Mar 2009 11:36:26 GMT

Please adopt following methods

1. Please write separate SELECT statments so query will be faster since join on three table takes lot of ~~memory~~ time.

2. User database view to select data from EKKO and EKPO and then select data from EKET table since you have user

 AND eket~rsnum NE space

to restrict EKET table.

3. Please get all the key fields in WHERE conditions.

Please use ST05 trace to check if indexes are used by optimizer to select data or table is scanned.

If it is scanned then you need to rewrite the WHERE clause.

Regards,

Sunil

Edited by: Sunil Sawaikar on Mar 23, 2009 12:38 PM

Edited by: Sunil Sawaikar on Mar 24, 2009 7:33 AM

Re: performance tuning problem

Former Member — Mon, 23 Mar 2009 12:13:12 GMT

> 1. Please write separate SELECT statments so query will be faster since join on

> three table takes lot of memory.

nonsense ... sorry, politeness does not help to stop this repeated nonsense.

This is simply incorrect!

Re: performance tuning problem

Former Member — Tue, 24 Mar 2009 05:20:28 GMT

Hi,

we are filling ekorg and werks feilds in the selection screen.

Re: performance tuning problem

Former Member — Tue, 24 Mar 2009 05:38:09 GMT

hi:

i would advice to use function module ADSPCM_READ_EKPO_ALL, instead of select statement

Where the matter of conditions are concerned , which can be taken in under loops.

Regards

Shashi

Re: performance tuning problem

Former Member — Tue, 24 Mar 2009 13:53:25 GMT

Hi sashi,

That function module ADSPCM_READ_EKPO_ALL is not available in my system.

is it standard one?

Thanks,

Dharani.