performance tuning problem

[Former Member]

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.

[Former Member]

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 !!!!

[Former Member]

Hi,

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

[Former Member]

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

[Former Member]

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

[Former Member]

> 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!

[Former Member]

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

[Former Member]

Hi sashi,

That function module ADSPCM_READ_EKPO_ALL is not available in my system.

is it standard one?

Thanks,

Dharani.