July 10, 2009 at 2:10 pm
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.
July 10, 2009 at 2:19 pm
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"
July 10, 2009 at 2:24 pm
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.
July 10, 2009 at 2:25 pm
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
July 10, 2009 at 8:54 pm
July 10, 2009 at 9:15 pm
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.
July 13, 2009 at 7:09 am
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.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply