T-SQL Performance Question

Question

Post reply

T-SQL Performance Question

Vicki Peters

SSCommitted

Points: 1866
More actions
November 5, 2009 at 8:00 am

#139261

Hi,
Does anyone know if there is any difference in performance when you put your criteria with your INNER JOIN rather than in your WHERE clause?
For example:
SELECT t1.Id
FROM dbo.Table1 AS t1
JOIN dbo.Table2 AS t2 ON t1.Id = t2.Id
AND t2.Active = 1
Verses:
SELECT t1.Id
FROM dbo.Table1 AS t1
JOIN dbo.Table2 AS t2 ON t1.Id = t2.Id
WHERE t2.Active = 1

Viewing 4 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Probably no difference at all.

Check the execution plans for two queries like that, see if they're different in any way.

- 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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Vicki Peters (11/5/2009)
Hi,
Does anyone know if there is any difference in performance when you put your criteria with your INNER JOIN rather than in your WHERE clause?
For example:
SELECT t1.Id
FROM dbo.Table1 AS t1
JOIN dbo.Table2 AS t2 ON t1.Id = t2.Id
AND t2.Active = 1
Verses:
SELECT t1.Id
FROM dbo.Table1 AS t1
JOIN dbo.Table2 AS t2 ON t1.Id = t2.Id
WHERE t2.Active = 1

Heh... why ask us?? Just try it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Inner join, no. Left/right join, they're two different queries with two different results, so performance isn't the issue....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass