SQL with multipe Inner joins - Performance (UNION ALL)

[Former Member]

I have the following SQL:

SELECT DISTINCT

cciht_clm~recn

cciht_clm~recnroot

cciht_clm~clmfileno

cciht_ip~ippers

INTO TABLE lt_select

• INTO (lw_recn,

• lw_recnroot,

• wa_active-clmfileno,

• wa_active-ein)

FROM ( ( cciht_ipeva

INNER JOIN cciht_clm ON cciht_clm_{recntipeva = cciht_ipeva}recn AND

cciht_clm_{recnroot = cciht_ipeva}recnroot )

INNER JOIN cciht_ip ON cciht_ip_{recn = cciht_ipeva}recntip AND

cciht_ip_{recnroot = cciht_ipeva}recnroot )

FOR ALL entries IN lt_keys

WHERE cciht_ipeva~recnroot = lt_keys-recnroot

AND cciht_ipeva~valfr LE sy-datum

AND cciht_ipeva~valto GE sy-datum

AND cciht_clm~recn = lt_keys-recnclm

AND cciht_clm~recnroot = lt_keys-recnroot

AND cciht_clm~valfr LE sy-datum

AND cciht_clm~valto GE sy-datum

AND cciht_ip~recnroot = lt_keys-recnroot

AND cciht_ip~iptype = 'INJ'

AND cciht_ip~valfr LE sy-datum

AND cciht_ip~valto GE sy-datum.

The performance is bad. When I run a trace and do EXPLAIN, I notice it does UNION ALL's.

Explanation of query block number: 1 step: 1

Query block type is UNION ALL

Performance appears to be bad

Method:

. access new table.

. unknown or no prefetch is used

. new table:

. .

. table space locked in mode:

Any ideas on how I can optimize this statement?

[Former Member]

Try avoiding more than one table in join.

Please also check that index is being used for each database fetch.

Also , Chech internal table lt_keys for initial before usinf FOR ALL ENTRIES.

Edited by: Harsh Bhalla on Oct 30, 2009 10:02 AM

[ThomasZloch]

> Try avoiding more than one table in join.

This is quite funny actually

Thomas

[Former Member]

How would you use a join with a single table? Join to itself?

[Former Member]

WHERE cciht_ipeva~recnroot = lt_keys-recnroot
AND cciht_ipeva~valfr LE sy-datum
AND cciht_ipeva~valto GE sy-datum
AND cciht_clm~recn = lt_keys-recnclm
AND cciht_clm~recnroot = lt_keys-recnroot
AND cciht_clm~valfr LE sy-datum
AND cciht_clm~valto GE sy-datum
AND cciht_ip~recnroot = lt_keys-recnroot
AND cciht_ip~iptype = 'INJ'
AND cciht_ip~valfr LE sy-datum
AND cciht_ip~valto GE sy-datum. 

this is somehow nonsense. 2/3 of it are ON-conditions for the JOIN.

Please read somewhere in Wikipedia or so, what indices can do, and decide which table should be used first.

Write conditions for that table and ON-conditions for the other one.

You don't need the DISTINCT, the FAE is distinct on the total result.

Siegfried

[Former Member]

Join to itself?

why not ?

[Former Member]

Hello Seigfried,

Is there any peculiar situation where we may have to use a join on a table to itself? I know its syntactically correct and works. But dont see any reason why would anyone use it.

Vikranth

[ThomasZloch]

I can only think of father/child relationships between different rows of the same table, e.g. table ESLH.

Thomas

[HermannGahm]

and for performance tuning in can make sense to do so.

E.g. in order to "delay" a table access.

[Former Member]

How am joining table to itself?

The INNER joins are between IPEVA and IP & IPEVA and CLM

[Former Member]

I don't think you are - the discussion got side tracked.

Rob

[Former Member]

Oops!! Sorry Raynald. I just used your thread space for a suggestion , thoughts as to when self-joins will be useful.

Thank you Thomas, Hermann and Seigfried for the examples quoted.

[HermannGahm]

Hi,

i structured your statement a little bit:

FROM ( ( cciht_ipeva
INNER JOIN cciht_clm 
ON cciht_clm~recntipeva = cciht_ipeva~recn 
AND cciht_clm~recnroot = cciht_ipeva~recnroot )

INNER JOIN cciht_ip 
ON cciht_ip~recn = cciht_ipeva~recntip 
AND cciht_ip~recnroot = cciht_ipeva~recnroot )

FOR ALL entries IN lt_keys

WHERE cciht_ipeva~recnroot = lt_keys-recnroot
AND cciht_ipeva~valfr LE sy-datum
AND cciht_ipeva~valto GE sy-datum

AND cciht_clm~recn = lt_keys-recnclm
AND cciht_clm~recnroot = lt_keys-recnroot
AND cciht_clm~valfr LE sy-datum
AND cciht_clm~valto GE sy-datum

AND cciht_ip~recnroot = lt_keys-recnroot
AND cciht_ip~iptype = 'INJ'
AND cciht_ip~valfr LE sy-datum
AND cciht_ip~valto GE sy-datum.

use the values from one line in of your internal table lt_keys:

Apply the where conditions for each table in the where condition blocks in se16 and

check the result set for each table. For the table with the smalles result set check if

the where conditions are supported with indexes. For the other tables check if the

JOIN conditions (ON) are supported with indexes. And as Siegfried said you can

ommit the distinct and those where conditions that are not the starting point and are

used in the joins. If necessary create supporting indexes.

when the execution plan on DB2 says: "Performance appears to be good" or "Performance appears to be excellent"

your have done a good job

Kind regards,

Hermann

[Former Member]

there are of course demands for self-joins for functional reasons:

There is or was a table in the SAP-standard which was nearly only accessed by slef-join, that is the AUSP

it contains properties of materials

material property property_value

A color blue

A size 1m

B ...

You want all material with color 'blue' and size '1m'.

Or check DD03L, tabnames and fieldnames, select all tables which have the fields 'spras' and 'country'.

Siegfried

[Former Member]

@Rob,

self-joins are rarely asked here.

There actual question is very common, nothing and has already enough answers. Checks for indizes and learn about ON-conditions.

Siegfried