July 10, 2009 at 12:51 pm
So, first things first, I apologize for a lack of code, I haven't been able to come up with any suitably annonimized data that will represent my issue.
The question I have is, does the order of tables in the from line of a query cause a difference in the results one would expect? I'm not speaking of the differences of putting your filter criterion in the join syntax vs the where clause, I'm talking about only changing the order of the tables listed in the from clause...
Consider the following...
Query 1
SELECT t2.[Identifier]
FROM Tab1 t1
INNER JOIN Tab2 t2
ON t1.[Identifier] = t2.[Identifier]
INNER JOIN Tab3 t3
ON t3.fktoTab2 = t2.id
where t3.value in (1,2,3,7)
and t1.type = 4
and t2.[Identifier] = 123456
Query 2
SELECT t2.[Identifier]
FROM Tab2 t2
INNER JOIN Tab3 t3
ON t2.id = t3.fktoTab2
INNER JOIN Tab1 t1
ON t2.[Identifier] = t1.[Identifier]
WHERE t3.value in (1,2,3,7)
and t1.type = 4
and t2.[Identifier] = 123456
To my human eyes, these two look like they should return the same recordset, however when they are executed, the return different results with different plans. This is on a Sql server 2000 SP 2 instance. any one out there remember any issues with the Order of tables in the FROM line and SQL 2k SP2?
Thanks in advance.
-Luke.
July 10, 2009 at 1:08 pm
I've never seen it cause a difference in results. Not with 2k, not with 2k5, not with 2k8.
Not going to say it can't happen, just that I've never seen it happen.
Are you positive that it returns different results, and not just something like the same results sorted differently?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 1:14 pm
Positive, if I run both queries in the same batch, I get 2 resultsets, one that's blank and one with 2 records... It's not that I'm dealing with 100's or even thousands of rows being returned...
Just found it rather odd. Initially, I saw the differences in the 2 querys as I was tuning one from an old developer who'd used the old style of join, which usually isn't a problem for inner joins. I changed it to the Ansi 92 syntax and it still brings back a different result set. That's when I figured I'd post it up here cause I've no idea at this point...
-Luke.
July 10, 2009 at 1:19 pm
Can you post the execution plans?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 1:25 pm
GSquared (7/10/2009)
I've never seen it cause a difference in results. Not with 2k, not with 2k5, not with 2k8.Not going to say it can't happen, just that I've never seen it happen.
Are you positive that it returns different results, and not just something like the same results sorted differently?
Our PeopleSoft developers have run into this when converting old style INNER joins to ANSI style inner joins.
The problem could be this:
select
t1.col1,
t2.col2,
t3.col3
from
dbo.table1 t1,
dbo.table2 t2,
dbo.table3
where
t1.col1 = t2.col1 and
t2.col2 = t3.col1 and
t1.col4 = somevalue and
t3.col4 = someothervalue
does not equal
select
t1.col1,
t2.col2,
t3.col3
from
dbo.table1 t1
inner join dbo.table2 t2
on t1.col1 = t2.col1
inner join dbo.table3
on t2.col2 = t3.col1
where
t1.col4 = somevalue and
t3.col4 = someothervalue
but instead
select
t1.col1,
t2.col2,
t3.col3
from
dbo.table1 t1
inner join dbo.table2 t2
on t1.col1 = t2.col1
and t1.col4 = somevalue
inner join dbo.table3
on t2.col2 = t3.col1
and t3.col4 = someothervalue
Edit: Or, depending on the criteria in the WHERE clause, any other permutation that may exist.
July 10, 2009 at 1:37 pm
Yes, I've definitely seen differences with the math in Where vs Join. But the above two don't appear to have that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 1:41 pm
That's because we don't have the data to see what is going on behind the scenes. I'll wait and see what Luke comes back with on this one.
July 10, 2009 at 1:45 pm
Right, that's my issue with it also. At first I thought it was the where vs join issue... but all fo the filtering is being done in the where statement in the 2 examples I provided. the only difference is the order of the in the FROM statement.
I'm working to try to remove all references to our private data from the execution plans, might have to wait until later tonight when I get home though...
Oh and by the way I just tried this on a 2005 SP3 instance (set in 80 compat mode however) and it reacted the same way.
-Luke.
July 10, 2009 at 1:50 pm
I have seen various execution plans depending on the order of tables in the FROM part.
In most cases, there will be no difference because SQL Server rearranges them in an optimal way.
This is also one of the areas where you can beat the SQL optimizer. I was a consultant at H&M where I rewrote several of their bad performing queries by rearranging the tables in the order of least records to most records and added KEEPORDER hint to the query.
In 80% of the cases, a more optimal query plan was generated!
Yes, we did try RECOMPILE and OPTIMIZE FOR hints too, but the rearrangments and KEEPORDER hint beat them all.
One query went down from 120,000,000 reads down to 90,000 reads just by rearranging the tables in the order from least records to most records.
N 56°04'39.16"
E 12°55'05.25"
July 10, 2009 at 1:55 pm
Lynn Pettis (7/10/2009)
That's because we don't have the data to see what is going on behind the scenes. I'll wait and see what Luke comes back with on this one.
Which query returns 0 records and which query returns 2 rows?
July 10, 2009 at 1:55 pm
Have you flushed the cache (obviously not in production) and then run the queries? I've never seen table order with all INNER JOIN's affect the results. Even if you get different execution plans like Peso mentions you should get the same data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 2:01 pm
Lynn Pettis (7/10/2009)
Lynn Pettis (7/10/2009)
That's because we don't have the data to see what is going on behind the scenes. I'll wait and see what Luke comes back with on this one.Which query returns 0 records and which query returns 2 rows?
Query 1 return 0 rows, query 2 returns 2 rows.
-Luke.
July 10, 2009 at 2:06 pm
No, haven't flushed the cache, I have only been able to id the problem on my Prod instance at the moment, I'm waiting on a maintenance window this weekend to try and get some of those types of things taken care of....
Here's the execution plans or at least as close to the real thing as I can give you...
-Luke.
Query 1
SELECT *
FROM Tab1 t1
INNER JOIN Tab2 t2
ON t1.[Identifier] = t2.[Identifier]
INNER JOIN Tab3 t3
ON t3.fktoTab2 = t2.id
where t3.value in (1,2,3,7)
and t1.type = 4
and t2.[Identifier] = 123456
|--Nested Loops(Inner Join)
|--Filter(WHERE:(tab3.value as [t3].[value]=(1) OR tab3.value as [t3].[value]=(2) OR tab3.value as [t3].[value]=(3) OR tab3.value as [t3].[value]=(7)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t3].[ID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[ID]))
| | |--Index Seek(OBJECT:([tab2].[IdentifierIndex] AS [t2]), SEEK:(t2.[Identifier] =(123456)) ORDERED FORWARD)
| | |--Index Seek(OBJECT:([tab3].[fktoTab2Index] AS [t3]), SEEK:([t3].[fktoTab2]=[tab2].[ID] as [t2].[ID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([tab3].[PK_tab3] AS [t3]), SEEK:([t3].[ID]=[tab3].[ID] as [t3].[ID]) LOOKUP ORDERED FORWARD)
|--Filter(WHERE:([Tab1].[Type] as [t1].[Type]=(4)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[ID]) OPTIMIZED)
|--Index Seek(OBJECT:([Tab1].[tab1idx83] AS [t1]), SEEK:([t1].[Case]=(123456)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Tab1].[tab1_PK] AS [t1]), SEEK:([t1].[ID]=[Tab1].[ID] as [t1].[ID]) LOOKUP ORDERED FORWARD)
Query 2
SELECT *
FROM Tab2 t2
INNER JOIN Tab3 t3
ON t2.id = t3.fktoTab2
INNER JOIN Tab1 t1
ON t2.[Identifier] = t1.[Identifier]
WHERE t3.value in (1,2,3,7)
and t1.type = 4
and t2.[Identifier] = 123456
|--Filter(WHERE:(tab3.value as [t3].[value]=(1) OR tab3.value as [t3].[value]=(2) OR tab3.value as [t3].[value]=(3) OR tab3.value as [t3].[value]=(7)
|--Nested Loops(Inner Join, OUTER REFERENCES:([t3].[ID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[ID]))
| |--Nested Loops(Inner Join)
| | |--Filter(WHERE:([Tab1].[Type] as [t1].[Type]=(4)))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[ID]) OPTIMIZED)
| | | |--Index Seek(OBJECT:([Tab1].[tab1idx83] AS [t1]), SEEK:([t1].[Identifier]=(123456)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([Tab1].[tab1_PK] AS [t1]), SEEK:([t1].[ID]=[Tab1].[ID] as [t1].[ID]) LOOKUP ORDERED FORWARD)
| | |--Index Seek(OBJECT:([tab2].[IdentifierIndex] AS [t2]), SEEK:(t2.[Identifier] =(123456)) ORDERED FORWARD)
| |--Index Seek(OBJECT:([tab3].[fktoTab2Index] AS [t3]), SEEK:([t3].[fktoTab2]=[tab2].[ID] as [t2].[ID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([tab3].[PK_Statutues] AS [t3]), SEEK:([t3].[ID]=[tab3].[ID] as [t3].[ID]) LOOKUP ORDERED FORWARD)
July 10, 2009 at 2:08 pm
I could see a different execution plan being generated - but not different results. Both queries should return the same results and the fact that they are different says the queries are different.
However, I don't see that the queries are semantically different in any way.
Are any of the key columns in either table datetime columns?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 10, 2009 at 2:08 pm
egads! apparently I just found another bug with the code="SQL" windows....
here they are in plain text...
Q1
SELECT *
FROM Tab1 t1
INNER JOIN Tab2 t2
ON t1.[Identifier] = t2.[Identifier]
INNER JOIN Tab3 t3
ON t3.fktoTab2 = t2.id
where t3.value in (1,2,3,7)
and t1.type = 4
and t2.[Identifier] = 123456
|--Nested Loops(Inner Join)
|--Filter(WHERE:(tab3.value as [t3].[value]=(1) OR tab3.value as [t3].[value]=(2) OR tab3.value as [t3].[value]=(3) OR tab3.value as [t3].[value]=(7)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t3].[ID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[ID]))
| | |--Index Seek(OBJECT:([tab2].[IdentifierIndex] AS [t2]), SEEK:(t2.[Identifier] =(123456)) ORDERED FORWARD)
| | |--Index Seek(OBJECT:([tab3].[fktoTab2Index] AS [t3]), SEEK:([t3].[fktoTab2]=[tab2].[ID] as [t2].[ID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([tab3].[PK_tab3] AS [t3]), SEEK:([t3].[ID]=[tab3].[ID] as [t3].[ID]) LOOKUP ORDERED FORWARD)
|--Filter(WHERE:([Tab1].[Type] as [t1].[Type]=(4)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[ID]) OPTIMIZED)
|--Index Seek(OBJECT:([Tab1].[tab1idx83] AS [t1]), SEEK:([t1].[Case]=(123456)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Tab1].[tab1_PK] AS [t1]), SEEK:([t1].[ID]=[Tab1].[ID] as [t1].[ID]) LOOKUP ORDERED FORWARD)
Q2
SELECT *
FROM Tab2 t2
INNER JOIN Tab3 t3
ON t2.id = t3.fktoTab2
INNER JOIN Tab1 t1
ON t2.[Identifier] = t1.[Identifier]
WHERE t3.value in (1,2,3,7)
and t1.type = 4
and t2.[Identifier] = 123456
|--Filter(WHERE:(tab3.value as [t3].[value]=(1) OR tab3.value as [t3].[value]=(2) OR tab3.value as [t3].[value]=(3) OR tab3.value as [t3].[value]=(7)
|--Nested Loops(Inner Join, OUTER REFERENCES:([t3].[ID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[ID]))
| |--Nested Loops(Inner Join)
| | |--Filter(WHERE:([Tab1].[Type] as [t1].[Type]=(4)))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[ID]) OPTIMIZED)
| | | |--Index Seek(OBJECT:([Tab1].[tab1idx83] AS [t1]), SEEK:([t1].[Identifier]=(123456)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([Tab1].[tab1_PK] AS [t1]), SEEK:([t1].[ID]=[Tab1].[ID] as [t1].[ID]) LOOKUP ORDERED FORWARD)
| | |--Index Seek(OBJECT:([tab2].[IdentifierIndex] AS [t2]), SEEK:(t2.[Identifier] =(123456)) ORDERED FORWARD)
| |--Index Seek(OBJECT:([tab3].[fktoTab2Index] AS [t3]), SEEK:([t3].[fktoTab2]=[tab2].[ID] as [t2].[ID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([tab3].[PK_Statutues] AS [t3]), SEEK:([t3].[ID]=[tab3].[ID] as [t3].[ID]) LOOKUP ORDERED FORWARD)
Edit: all frowny faces should be ([
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply