performance Tuning

[Former Member]

HI ,I have question abouth performance tuning about my problem.

SELECT ekbe~ebeln ebelp gjahr belnr

buzei vgabe zekkn bewtp

shkzg dmbtr arewr

INTO TABLE it_ekbe_y

FROM ekbe INNER JOIN ekko

ON ekbe_{ebeln = ekko}ebeln

WHERE ( bewtp = 'E' OR bewtp = 'Q' )

AND budat GT i_budat

AND budat LE sy-datum

AND lifnr EQ g_lifnr.

when i using this inner join in my prd system ,the performance is very poor,and my system will dump.because "time out'.

in these table: records

ekbe:46,448,764

ekko:9,653,810

if i using 'for all entries', the it_ekko records also very large, i think maybe the performance not will be tuned. so who can help me to solve it ? except 'for all entries,' have other methods ?

SELECT ekpo~ebeln ebelp netwr mwskz

INTO TABLE it_ekpo_y

FROM ekpo INNER JOIN ekko ON

ekpo_{ebeln = ekko}ebeln

WHERE pstyp EQ '0'

AND ekpo~loekz NE 'L'

AND elikz NE 'X'

AND retpo EQ 'X'

AND lifnr EQ g_lifnr

• AND ekko~aedat GT i_budat

• AND ekko~aedat LE sy-datum

AND frgke NE '0'

AND ( bsart EQ 'NB' OR bsart EQ 'Y01' OR bsart EQ 'Y02' ).

this select performance is very poor. when i using 'for all entries' performance is also very poor.

give me some suggestion.

thanks,

Susan

[Former Member]

HI,

Remove OR conditions and use ranges to improve performance ..

DATA : it_ekpo LIKE ekpo OCCURS 0 WITH HEADER LINE.
RANGES : r_bsart FOR ekko-bsart.

r_bsart-low = 'NB'.
r_bsart-sign = 'EQ'.
r_bsart-option = 'I'.
APPEND r_bsart.

r_bsart-low = 'Y01'.
r_bsart-sign = 'EQ'.
r_bsart-option = 'I'.
APPEND r_bsart.

r_bsart-low = 'Y02'.
r_bsart-sign = 'EQ'.
r_bsart-option = 'I'.
APPEND r_bsart.

SELECT ekpo~ebeln ebelp netwr mwskz
INTO TABLE it_ekpo
FROM ekpo INNER JOIN ekko ON
ekpo~ebeln = ekko~ebeln
WHERE pstyp EQ '0'
AND ekpo~loekz NE 'L'
AND elikz NE 'X'
AND retpo EQ 'X'
*AND lifnr EQ g_lifnr
AND frgke NE '0'
*AND ( bsart EQ 'NB' OR bsart EQ 'Y01' OR bsart EQ 'Y02' ).
AND bsart IN r_bsart. 

[valter_oliveira]

Hello.

This is caused because you are acessing a large database table without apropriate index. Some advices:

1 - create an index with fields bewtp/budat in ekbe table

2 - avoid using NE. nstead of elikz NE 'X', use elikz EQ space... it's faster

3 - instead of ( bewtp = 'E' OR bewtp = 'Q' ) use bewtp IN ('E','Q') ... it's faster.

4 - use tcode ST05 to check the result of your changes

Regards,

Valter Oliveira.

[Former Member]

First of all, there's probably no good reason to create an index, so avoid doing that.

Table EKKO has a secondary index on LIFNR, which you are using so the SELECT should be able to use that. Have you run a perfromance trace (ST05) to see if it does use that index?

Rob

[kesavadas_thekkillath]

i have a confusion basod on your aedat parameter...check whethre i have included it correctly..
try to join a master and transaction table...not transaction and master table...

data:v_ebeln type ekko-ebeln value is initial.

select ekpo~ebeln ebelp netwr mwskz into table it_ekpo_y from ekko
inner join ekpo on ekko~ebeln = ekpo~ebeln
where ekko~ebeln ge v_ebeln
and bukrs = 			"<----pass the company code Too
and ekko~bsart in ('NB','Y01','Y02')
and ekko~bstyp in ('F','L') "<--Ask ur functional abt this field...try to pass this..if needed include A and K also
and ekko~aedat between i_budat and sy-datum
and ekko~lifnr eq g_lifnr
and ekko~frgke ne '0'  <----Try to pass the possible params instead of NE  ....
and ekpo~elikz = space
and ekpo~bstyp =           <---Pass this too
and ekpo~bukrs = 	   <---Pass this too
and ekpo~werks =           <---pass this too
and ekpo~loekz = space

[Former Member]

Hi,

I guess it would be better if you split up your select query into two. Use one more internal table and get data into two internal tables separately, also use the indexes as suggested above. Then sort both internal tables and loop any one internal table (better go with that having lesser records). Inside that, do a read with key and if sy-subrc eq 0, move those records to the final internal table.

To my understanding, while using select query with inner join, it will do the process at data base level, but if we do some process (for checking and all) outside, time for data base fetch will reduce.

Regards,

Renjith