Order of tables in a from line

  • Jeffrey Williams (7/10/2009)


    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?

    Nope all of the PK/FK columns are integers.

    -Luke.

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

  • Is it possible for you to attach the execution plan in "graphical" or xml ?

    The *.sqlplan file will do!

    From what I can see the two executions plans are different and the filters are applied in different stages.


    N 56°04'39.16"
    E 12°55'05.25"

  • Sorry, unfortunately, I can't give you the graphical plans 'cause it doesn't allow me to remove "any and all references to our data" as specified by my boss. I work for a local government agency and they take that sort of stuff somewhat seriously.

    Yes, the filters are being applied at different stages. The question is why? Shouldn't the engine treat them as be basically the same query?

    -Luke.

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

  • I have to ask, please try the following queries:

    SELECT

    *

    FROM

    Tab2 t2

    INNER JOIN Tab3 t3

    ON t2.id = t3.fktoTab2 and

    t3.value in (1,2,3,7)

    INNER JOIN Tab1 t1

    ON t2.[Identifier] = t1.[Identifier] and

    t1.type = 4

    WHERE

    t2.[Identifier] = 123456

    and

    SELECT

    *

    FROM

    Tab1 t1

    INNER JOIN Tab2 t2

    ON t2.[Identifier] = t1.[Identifier] and

    t1.type = 4

    INNER JOIN Tab3 t3

    ON t2.id = t3.fktoTab2 and

    t3.value in (1,2,3,7)

    WHERE

    t2.[Identifier] = 123456

  • Lynn, I'll have to give them I try on Monday when I get back to the office. I'll let you know though.

    Thanks,

    -Luke.

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

  • Okay, but I won't know until Monday evening. I should hopefully be spending the day out on the pitch officiating games at the PPI up at the USAFA.

  • Lynn Pettis (7/10/2009)


    I have to ask, please try the following queries:

    SELECT

    *

    FROM

    Tab2 t2

    INNER JOIN Tab3 t3

    ON t2.id = t3.fktoTab2 and

    t3.value in (1,2,3,7)

    INNER JOIN Tab1 t1

    ON t2.[Identifier] = t1.[Identifier] and

    t1.type = 4

    WHERE

    t2.[Identifier] = 123456

    and

    SELECT

    *

    FROM

    Tab1 t1

    INNER JOIN Tab2 t2

    ON t2.[Identifier] = t1.[Identifier] and

    t1.type = 4

    INNER JOIN Tab3 t3

    ON t2.id = t3.fktoTab2 and

    t3.value in (1,2,3,7)

    WHERE

    t2.[Identifier] = 123456

    These have the exact same results as the previous queries with the same execution plan although it seems at some point the order of the queries was reversed. The one I'm calling Query 1 has the join order as t1 ij t2 ij t3 Where as Query 2 has the join order of t2 ij t3ij t1.

    Just noticed some of the posts may have had the order reversed and I Wanted to clear up any confusion before it got started. Also Query 1 returns no results while query 2 returns the 2 rows I'm expecting.

    Thanks,

    -Luke.

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

Viewing 7 posts - 16 through 22 (of 22 total)

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