May 21, 2008 at 5:51 am
HI all,
I looked for an article about this but had no luck.
I was wondering if there are any major performance differences between the following:
1)
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.ID = T2.ID
AND ISNULL(T1.Name,'') = ''
Is it better to put the ISNULL(T1.Name,'') = '' in a where clause?
2)
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.ID = T2.ID
AND T1.Name = @vcName
Is it better to put the T1.Name = @vcName in a where clause?
The Query Plans seem to be the same!
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 21, 2008 at 6:03 am
Ok further research has showing
1)
JOIN:
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 1306 ms.
WHERE:
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 1278 ms.
2)
JOIN:
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 8 ms.
WHERE:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 28 ms.
This still confuses me as the CPU and Elapsed Time seem to go in different directions ?!?!?!
Any thoughts would be great
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 21, 2008 at 6:25 am
except the queryplans, in case of outer joins, there can be different results.
Try this
DECLARE @TestData1 TABLE (
vKey int,
vValue varchar(20)
)
DECLARE @TestData2 TABLE (
vKey int,
vValue varchar(20)
)
INSERT INTO @TestData1 VALUES (1,'VALUE 1')
INSERT INTO @TestData1 VALUES (2,'VALUE 2')
INSERT INTO @TestData1 VALUES (3,'VALUE 3')
INSERT INTO @TestData1 VALUES (4,'VALUE 4')
INSERT INTO @TestData1 VALUES (5,'VALUE 5')
INSERT INTO @TestData2
SELECT * FROM @TestData1 WHERE vKey < 5
SELECT *
FROM @TestData1 t1 LEFT JOIN @TestData2 t2
ON t1.vKey = t2.vKey
AND t2.vValue LIKE 'VALUE [1-4]'
SELECT *
FROM @TestData1 t1 LEFT JOIN @TestData2 t2
ON t1.vKey = t2.vKey
WHERE t2.vValue LIKE 'VALUE [1-4]'
w.lengenfelder
May 21, 2008 at 6:29 am
Hi,
With regards to Left joins I would expect the data to be different.
Thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 21, 2008 at 6:38 am
The short answer is, it depends. The longer answer is, that in some cases, yes, it makes a huge difference to put the filters into the JOIN instead of the WHERE clause. The JOIN is processed before the WHERE clause. In some cases, information from the WHERE clause is incorporated right into the JOIN and in some cases it's filtered later. Putting criteria specifically in the JOIN criteria can limit the amount of data being processes sooner.
As was already pointed out, you have to be very wary of this in OUTER JOINS because it can completely change the behavior of the JOIN and the data returned. Generally, until tested, it's safer to only put the JOIN criteria in an OUTER join and leave any filtering for the WHERE clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2008 at 9:35 am
Hi Grant,
Thanks for the reply.
I'll keep that in mind and I'll keep testing my results for each situation that comes up in the future
Thanks again
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 21, 2008 at 1:17 pm
This still confuses me as the CPU and Elapsed Time seem to go in different directions ?!?!?!
This often happens when one query plan doesn't convince SQL Server to use parallel processing, and another plan does convince it.
(I'm assuming in your speed tests, you either ran each query multiple times, to get a feel for how it goes when it's cached, or cleared the cache between tests.)
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply