topic Re: Performance with join in Application Development and Automation Discussions

Performance with join

Former Member — Wed, 29 Mar 2006 04:24:40 GMT

Hi,

Could you please give me tips to improve the performmance of the following joins.

SELECT ekbe~ebeln

ekbe~ebelp

ekbe~vgabe

ekbe~matnr

ekbe~menge

ekbe~shkzg

ekpo~werks

ekpo~meins

ekpo~afnam

ekpo~lgort

INTO TABLE i_temp

FROM ekbe

INNER JOIN ekpo ON ekpo_{ebeln = ekbe}ebeln AND

ekpo_{ebelp = ekbe}ebelp

WHERE ekbe~cpudt IN s_date AND

ekpo~werks IN s_werks AND

ekbe~ebeln IN s_ebeln AND

( ekbe~vgabe EQ '1' ) OR

( ekbe~vgabe EQ '6' )

GROUP BY

ekbe~ebeln

ekbe~ebelp

ekbe~vgabe

ekbe~matnr

ekbe~menge

ekbe~shkzg

ekpo~werks

ekpo~meins

ekpo~afnam

ekpo~lgort.

DELETE i_temp WHERE werks NOT IN s_werks .

DELETE i_temp WHERE ebeln NOT IN s_ebeln.

Thanks & regards

Frank

Re: Performance with join

Former Member — Wed, 29 Mar 2006 04:26:57 GMT

Hi frank,

1. why do you want to use GROUP By ?

2. u are not using any aggregate functions in sql

like sum, count etc.

3. so if not required, you may remove

group by,

it will improve the performance a lot.

4.Further i think that

the BRACKETS for OR

are misplace. it should be like this :

ekpo~lgort

INTO TABLE i_temp

FROM ekbe

INNER JOIN ekpo ON ekpo_{ebeln = ekbe}ebeln AND

ekpo_{ebelp = ekbe}ebelp

WHERE ekbe~cpudt IN s_date AND

ekpo~werks IN s_werks AND

ekbe~ebeln IN s_ebeln AND

<b>( ekbe_{vgabe EQ '1' OR ekbe}vgabe EQ '6' )</b>

GROUP BY

regards,

amit m.

Re: Performance with join

Former Member — Wed, 29 Mar 2006 04:28:17 GMT

Hi Frank,

You can remove the GROUP BY statement.

Since you are not performing any aggregate functions .

You can do the same operation if any, in your internal table after the SELECT statement.

Regards,

Wenceslaus.

Re: Performance with join

Former Member — Wed, 29 Mar 2006 04:32:10 GMT

Hi Frank,

First you can remove the Group by clause .........as well as to check how much performance increase ....you can notice with the help of a tool code inspector(tcode-SCI)

please reward for the same.

Re: Performance with join

Former Member — Wed, 29 Mar 2006 04:37:47 GMT

HI Frank,

change the code like this..

 SELECT ekbe~ebeln
ekbe~ebelp
ekbe~vgabe
ekbe~matnr
ekbe~menge
ekbe~shkzg
INTO TABLE i_temp1.
FROM ekbe
WHERE ekbe~cpudt IN s_date AND 
ekbe~ebeln IN s_ebeln AND
( ekbe~vgabe EQ '1' ) OR
( ekbe~vgabe EQ '6' ).

if i_temp1[] is not initial.
SELECT
ekpo~werks
ekpo~meins
ekpo~afnam
ekpo~lgort
INTO TABLE i_temp2
FROM EKPO
FOR ALL ENTRIES in i_temp1
WHERE ekpo~werks IN s_werks.
endif.

MOVE CORRESPONDING i_temp1 to i_temp3.
MOVE CORRESPONDING i_temp2 to i_temp3.
append it_temp3.

here i_temp3 contains the finl structure that is needed..

then no need To use DELETE as the selection takes care of that..

regards

satesh

Re: Performance with join

Former Member — Wed, 29 Mar 2006 11:19:26 GMT

Hi Rex, In addition to wot amit already said (remove group by).

I think your SQL is already optimized to the max, and the reason is:

1) ( ekbe_{vgabe EQ '1' OR ekbe}vgabe EQ '6' )

- ekbe~vgabe IN ('1','6') will be slower in my openion

2) ekbe INNER JOIN ekpo

- two SQL staement (first on ekbe and then ekpo) will always give poor performance

Re: Performance with join

Former Member — Wed, 29 Mar 2006 15:40:43 GMT

I didn't really expect this, but when I ran this a couple of different ways with a trace on and then looked at the 'explain', I found the most important thing was to change:


( ekbe~vgabe EQ '1' ) OR
( ekbe~vgabe EQ '6' )

to:


( ekbe~vgabe EQ '1'   OR
  ekbe~vgabe EQ '6' )

(that is, if this is what you want). Additionaly, ensure that s_ebeln is not initial.

Rob

Re: Performance with join

Former Member — Wed, 29 Mar 2006 22:06:33 GMT

Hi,

The reason to use group by in my query is: The “group by” designation means that all data for a given STO will be the same for that field for all records in the internal table. Thus it can be combined to form one line. The Quantity field will be summed to determine the net amount issued or received.

Is the performance is okay if I change the following code?

( ekbe_{vgabe EQ '1' OR ekbe}vgabe EQ '6' )

Thanks & regards

Frank

Re: Performance with join

Former Member — Wed, 29 Mar 2006 22:18:10 GMT

It was better when I tried it. But you'll have to try it yourself before you know for sure.

Rob