Order of tables in a from line

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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

  • 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.

  • 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

  • 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.

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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"

  • 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?

  • 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.

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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)

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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

  • 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 ([

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply