patternsqlMajor
Index performance on ON versus WHERE
Viewed 0 times
indexwhereperformanceversus
Problem
I have two tables
These tables have a non-clustered index on (Id, Date)
And I join these tables
This can also be written as
My question is, which of these two queries gives the better performance and why? Or are they equal?
@T1 TABLE
(
Id INT,
Date DATETIME
)
@T2 TABLE
(
Id INT,
Date DATETIME
)These tables have a non-clustered index on (Id, Date)
And I join these tables
SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
t1.Id = t2.Id
WHERE
t1.Date <= GETDATE()
AND
t2.Date <= GETDATE()This can also be written as
SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
t1.Id = t2.Id
AND
t1.Date <= GETDATE()
AND
t2.Date <= GETDATE()My question is, which of these two queries gives the better performance and why? Or are they equal?
Solution
The performance will be the same. The optimizer will recognize this and create the same plan.
On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.
For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.
You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.
Gives these execution plans
On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.
For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.
You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.
SELECT * FROM salestable , custtable
WHERE salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid
SELECT * FROM salestable
JOIN custtable
ON salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid
SELECT * FROM salestable JOIN custtable
ON salestable.custaccount = custtable.accountnum
WHERE salestable.dataareaid = custtable.dataareaidGives these execution plans
Code Snippets
SELECT * FROM salestable , custtable
WHERE salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid
SELECT * FROM salestable
JOIN custtable
ON salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid
SELECT * FROM salestable JOIN custtable
ON salestable.custaccount = custtable.accountnum
WHERE salestable.dataareaid = custtable.dataareaidContext
StackExchange Database Administrators Q#105525, answer score: 32
Revisions (0)
No revisions yet.