topic Re: Optimising inner join in Application Development and Automation Discussions

Optimising inner join

Former Member — Tue, 25 Mar 2008 10:21:38 GMT

Hi,

I need to write a report that will show the next delivery that is due to arrive for a certain article and purchasing organisation.

My current SQL query is as follows:

SELECT min( eket~eindt )

INTO dDelivery

FROM ( ekpo INNER JOIN eket

ON eket_{ebeln EQ ekpo}ebeln

AND eket_{ebelp EQ ekpo}ebelp

INNER JOIN ekko

ON ekpo_{ebeln EQ ekko}ebeln )

WHERE ekpo~loekz EQ space

AND ekpo~matnr EQ ZSales_wa-matnr

AND ekpo~elikz EQ space

AND eket~eindt > dHTMLdatum

AND ekko~ekorg EQ vkorg_wa.

From where I am sitting this looks like it will take too much time to run for all articles.

Is there a better way to get the information (a function perhaps?) or otherwise a way I can optimise this selection?

Thanks in advance.

Charl

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 10:28:34 GMT

Dont MIN get all the values from the EKET then sort it take the first record

SELECT eket~eindt

INTO table dDelivery

FROM ( ekpo INNER JOIN eket

ON eket_{ebeln EQ ekpo}ebeln

AND eket_{ebelp EQ ekpo}ebelp

INNER JOIN ekko

ON ekpo_{ebeln EQ ekko}ebeln )

WHERE ekpo~loekz EQ space

AND ekpo~matnr EQ ZSales_wa-matnr

AND ekpo~elikz EQ space

AND eket~eindt > dHTMLdatum

AND ekko~ekorg EQ vkorg_wa.

sort dDelivery by eindt.

read table dDelivery index 1.

Regards

Shiva

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 11:09:33 GMT

Hi,

Santosh, your example seems to show the advantage of FOR ALL ENTRIES over a select/endselect.

Even in the example the double INNER JOIN is used.

Will you be able to provide an example based on what I have written to convert the INNER JOIN to FOR ALL ENTRIES and explain why it is faster/better?

Shivakumar, I use a similar tactic in other areas, but could you explain why your example is better to use in this case? Either way I end up with only one result. Yours I gain a bit more control over results checking, which I like, but I also end up using more memory and data has to go between SQL and my presentation server does it not?

Thanks for the quick responses.

Regards

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 11:18:46 GMT

It is not recommended to use MIN MAX commands which take more time

Since the tables are inter related and also they are header and item table

recently i improved the performance for the same using the tables only

if the tables are header and item it would be better to use joins rather than for all entries

one thing i want to say it is trial and error method to get the perfect performance

you can have for all entries for EKKO EKPO into one table and finaly get the EKET EINDT based on FOR all entries of first table

check this code which i have written recently

SELECT a~ebeln "po number

b~ebelp "po item

b~werks "plant

b~matkl "material grp

b~matnr "material

b~menge "qty

b~meins "unit

b~peinh "price unit

b~bprme "Order Price Unit

b~netpr "net price

b~retpo

b~loekz

b~elikz

a~lifnr

c~wemng

c~eindt

INTO TABLE lt_ekpo

FROM ekpo AS b

INNER

JOIN ekko AS a

ON a_{ebeln = b}ebeln

INNER

JOIN eket AS c

ON c_{ebeln = b}ebeln

AND c_{ebelp = b}ebelp

WHERE a~lifnr IN ilifnr1

AND b~werks IN s_werks

AND b~matkl IN imatkl1

AND c~eindt IN ideldate

AND a~bukrs EQ 'ULTA'

AND a~bsart EQ 'NB'

AND a~frgke NE 'B'

AND b~retpo NE 'X'

AND b~loekz NE 'L'

AND b~elikz NE 'X'.

regards

Shiva

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 12:06:18 GMT

Thanks Shiva.

One last bit.

Does the structure of the INNER JOIN influence the performance at all?

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 12:15:57 GMT

Sorry I dont have much idea

http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

Regards

Shiva

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 12:41:46 GMT

> Use FOR ALL ENTRIES statement instead of joins ..

> Check out the below link for a sample code for usage

> http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

completely weird recommendation, the example has a very complicated join and

additional FOR ALL ENTRIES ! From that example you can conclude nothing for the

problem here.

> Use FOR ALL ENTRIES statement instead of joins

this in incorrect, joins can be much better than for all entries, but only up to about 5 tables.

With more tables the optimizer will often not find the right order to process the tables.

Here you should start with the simple select:


SELECT *
INTO it_ekpo
FROM  ekpo 
WHERE ekpo~loekz EQ space
AND ekpo~matnr EQ ZSales_wa-matnr
AND ekpo~elikz EQ space.

compared to the join. I think already this select will not be very fast as there is no good index support.

Run both in your system and check the SQL trace

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

The join conditions are quite simple on primary keys and should work.

Siegfried

Re: Optimising inner join

Former Member — Tue, 25 Mar 2008 13:30:54 GMT

Thanks Siegfried.

I'll keep it as is then.

When time allows I'll separate the segments of code and experiment with the SQL trace. Nice blog entry.