Nested loop outer join question
-
I've got a simple SQL...
select * from table1 a
join table2 bon a.id_rssd = b.id_rssd
left outer join table3 con a.id_rssd = c.id_rssd
where a.id_rssd = 12932
and a.dt_start = 98321212
When looking at the query execution plan, there is a warning on the Nested Left Outer Loop saying "warning no join predicates". Clearly that isn't the case. I'm trying to solve a much more complex SQL, and in analyzing that I noticed this. I've stripped everything else down to this SQL. SQL Server seems to say this on any left outer join I code?
Is this correct? Perhaps I never noticed it before.
-
Can you post the plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Not very readable, but here it is. The "text" doesn't show the warning message however. You only see that when looking at the execution plan diagram and hovering over the nest loop box.
|--Nested Loops(Left Outer Join)
|--Nested Loops(Inner Join)
| |--Nested Loops(Inner Join, OUTER REFERENCES ([a].[id_rssd], [a].[d_dt_start]))
| | |--Index Seek(OBJECT ([xxxxxx].[dbo].[table1].[CUXATTRC] AS [a]), SEEK ([a].[id_rssd]=(12932) AND [a].[dt_start]=(98321212)) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT ([xxxxxx].[dbo].[table1].[PK__table1_pk] AS [a]), SEEK ([a].[id_rssd]=[xxxxxx].[dbo].[table1].[id_rssd] as [a].[id_rssd] AND [a].[d_dt_start]=[xxxxxx].[dbo].[table1].[d_dt_start] as [a].[d_dt_start]) LOOKUP ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT ([xxxxxx].[dbo].[table2].[PK__table2_pk ] AS ), SEEK (.[ID_RSSD]=(12932)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT ([xxxxxx].[dbo].[table3].[PK__table3_PK] AS [c]), SEEK ([c].[ID_RSSD]=(12932)) ORDERED FORWARD)
-
John,
Gail is asking you to save the execution plan as a .sqlplan and attach it to your post. Do you know how to do this?
-
sorry - I don't. Can you give me the steps?
-
When executing your query in SSMS, go to Query -> Include Actual Execution Plan. Alternately, if you don't want to run the query, you can use Query -> Display Actual Execution Plan, but it's not as accurate as the runtime version.
Once the Execution Plan tab pops up, click on the tab so it's in front, then go to File -> Save Execution Plan As... Give it a name and a destination on your hard drive. Attach it to your next post.
-
I figured it out...
-
Try moving the WHERE clause into the ON clause of the OUTER JOIN.
Also, make sure your statistics are up to date.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Moving the WHERE criteria did not affect the execution plan. Still "no join predicates".
-
-
Sounds similar but it's old - going back to SQL 2000. I did look at the order of my JOIN's and it is picking the INNER first, so it wouldn't appear that OPTION(FORCE ORDER) would be applicable to me.
I'm curious - can somebody try putting together a query that does a couple of JOIN's (a combo of inner and outer) on one of their databases and see if you get the warning. I've tried a SQL on all my environments (DEV/TEST/QA/PROD) and I get the same execution path. I'm going to try a completely different database and server and see if I get the same behavior. After that perhaps I should contact Microsoft.
-
John,
Your first post indicates that your real code is much more complicated than the code you posted in this thread. Is that true?
If so, can you post the code so we can try to duplicate what you're doing? And some DDL / Sample data too, please. That will help us approximate your issue as close as possible.
-
john.p.lantz (5/17/2012)
Sounds similar but it's old - going back to SQL 2000. I did look at the order of my JOIN's and it is picking the INNER first, so it wouldn't appear that OPTION(FORCE ORDER) would be applicable to me.I'm curious - can somebody try putting together a query that does a couple of JOIN's (a combo of inner and outer) on one of their databases and see if you get the warning. I've tried a SQL on all my environments (DEV/TEST/QA/PROD) and I get the same execution path. I'm going to try a completely different database and server and see if I get the same behavior. After that perhaps I should contact Microsoft.[/quote
It's not on all systems. This works fine in Adventureworks:
SELECT soh.CurrencyRateID,sod.ModifiedDate,
sop.ModifiedDate
FROM sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
LEFT JOIN Sales.SpecialOfferProduct AS sop
ON sod.SpecialOfferID = sop.SpecialOfferID
AND sod.ProductID = sop.ProductID
WHERE soh.CustomerID = 29566;
That's why I'm curious about whether or not stats are up to date. But, worth asking, are you on the latest service pack?
Something is up there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
john.p.lantz (5/17/2012)
I've got a simple SQL...select * from table1 a
join table2 bon a.id_rssd = b.id_rssd
left outer join table3 con a.id_rssd = c.id_rssd
where a.id_rssd = 12932
and a.dt_start = 98321212
When looking at the query execution plan, there is a warning on the Nested Left Outer Loop saying "warning no join predicates". Clearly that isn't the case. I'm trying to solve a much more complex SQL, and in analyzing that I noticed this. I've stripped everything else down to this SQL. SQL Server seems to say this on any left outer join I code?
Is this correct? Perhaps I never noticed it before.
The optimizer sees the join predicate [a.id_rssd = c.id_rssd] and the WHERE clause predicate [a.id_rssd = 12932], and implies [c.id_rssd = 12932] from those two pieces of information. It pushes the resulting implied predicate [c.id_rssd = 12932] down from the join to produce a seek on the table aliased as 'c'. The join no longer has a predicate, and this triggers the warning. More details here: http://blogs.msdn.com/b/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx
The warning is therefore benign in this case (the optimizer is careful to preserve outer join semantics). The warning is useful as a clue to the query writer to check the query is correctly written. Outer joining to a result that is not correlated with the driving input is a little unusual.
-
Grant Fritchey (5/18/2012)
It's not on all systems. This works fine in Adventureworks...Here's an Adventure Works example, in case it is helpful:
SELECT
sod.SalesOrderID,
th.TransactionID
FROM Production.TransactionHistory AS th
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = th.ReferenceOrderID
WHERE
th.ReferenceOrderID = 53480;
The warning won't appear in all plans that fit the pattern (where implied predicates means a join is left without a predicate) because the warning is only triggered in certain circumstances (e.g. depending on cardinality estimates).
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply