May 24, 2010 at 10:11 am
Hi,
One of my developers has sent me some code for optimization and she has managed to stump me with it. The code looks as though it has been auto generated whilst creating a view.
The code includes a right join without an on clause and for some reason the query runs without a problem.
The left join below is includes two ON clases and again this doesn't seem to be resulting in an error.
Could someone advise what is going on here? Code is as follows:
"
FROM scheme.cocontypm
INNER JOINscheme.coconcdsm ON scheme.cocontypm.contract_type = scheme.coconcdsm.contract_type
LEFT JOIN scheme.cfanalm ON scheme.coconcdsm.analysis1 = scheme.cfanalm.analysis_code
RIGHT JOIN scheme.ophdcontm ON scheme.coconcdsm.contract_code = scheme.ophdcontm.contract_code
RIGHT JOIN scheme.slcustm
INNER JOIN scheme.slitemm ON scheme.slcustm.customer = scheme.slitemm.customer
LEFT JOIN scheme.opheadm
ON scheme.slitemm.customer = scheme.opheadm.customer
AND scheme.slitemm.item_no = scheme.opheadm.invoice_no
ON scheme.ophdcontm.order_no = scheme.slitemm.item_no
"
May 24, 2010 at 10:32 am
This is a nested join. I would enforce the use of brackets and formatting:
FROM scheme.cocontypm
INNER JOIN scheme.coconcdsm
ON scheme.cocontypm.contract_type = scheme.coconcdsm.contract_type
LEFT JOIN scheme.cfanalm
ON scheme.coconcdsm.analysis1 = scheme.cfanalm.analysis_code
RIGHT JOIN scheme.ophdcontm
ON scheme.coconcdsm.contract_code = scheme.ophdcontm.contract_code
RIGHT JOIN scheme.slcustm
(
INNER JOIN scheme.slitemm
ON scheme.slcustm.customer = scheme.slitemm.customer
LEFT JOIN scheme.opheadm
ON scheme.slitemm.customer = scheme.opheadm.customer
AND scheme.slitemm.item_no = scheme.opheadm.invoice_no
)
ON scheme.ophdcontm.order_no = scheme.slitemm.item_no
May 25, 2010 at 1:04 am
The SQL Server optimiser only ever considers plan shapes that involves at least one base table in every join.
Your query is written in one of the two possible ways to force a plan which joins the result of one join to the result of another join directly. This is known as a 'bushy' plan, from the shape of the plan produced.
The following query demonstrates this (based loosely on the provided query). Take a look at the shape of the graphical plan.
DECLARE @a TABLE (A INT NULL);
DECLARE @b-2 TABLE (B INT NULL);
DECLARE @C TABLE (C INT NULL);
DECLARE @D TABLE (D INT NULL);
DECLARE @E TABLE (E INT NULL);
DECLARE @F TABLE (F INT NULL);
SELECT *
FROM @a A
JOIN @b-2 B
ON A.A = B.B
LEFT
JOIN @C C
ON C.C = B.B
RIGHT
JOIN @D D
ON D.D = C.C
RIGHT
JOIN (
@E E
JOIN @F F
ON F.F = E.E
)
ON F.F = C.C
OPTION (FORCE ORDER); -- Just to show the intended join operations
SELECT *
FROM @a A
JOIN @b-2 B
ON B.B = A.A
JOIN @C C
ON C.C = B.B
JOIN @D D
ON D.D =C.C
RIGHT
JOIN (
@E E
JOIN @F F
ON F.F = E.E
)
ON F.F = C.C
OPTION (FORCE ORDER); -- To show the two query forms are equivalent
Notice that the query is almost certainly incorrect as written, since the execution plan only contains one outer join - the optimiser is smart enough to detect that the other outer joins are in fact inner joins. You should add the OPTION (FORCE ORDER) hint to your plan (to prevent the optimiser re-ordering the joins) to show the developer that the logical effect of the query is not what he or she had in mind.
Forcing a bushy plan is an advanced query optimisation technique, and should only be applied where the implications are fully understood.
The bug concerning the outer joins strongly suggests that this is not in fact an example of a well-applied advanced optimisation 😉
You should go through the query with the developer and encourage (demand) that it is rewritten in standard form (no bushy plan) and verified to be correct before you consider such an advanced optimisation technique.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 25, 2010 at 1:17 am
Thanks very much for your advice guys; i'll certainly be sitting down with the developer to go through the code.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply