July 11, 2008 at 5:45 am
I've got nothing. The examples I thought I had, I can't find.
My heartfelt apologies for putting out bad information. For an INNER JOIN, it really makes no difference in terms of physical processing whether the filtering information is in the ON clause or the WHERE clause. I will add, as others have stated, it's still a very good practice to put the JOIN criteria into the ON clause as a mechanism for clarification and self-documentation, but not for a specific change in the result set or performance.
"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
July 11, 2008 at 6:17 am
Hi Grant,
what makes you sure this time that it does not make a difference?
Did you get this information from Microsoft ?
I am pretty sure that I had examples in the past where I had differences, especially with complex queries.
But I cannot say for sure that I did not mess up one of the queries when moving the condition around.
If you have some evidence or explanation as to why this could not happen, it would be great if you could let me know.
Thanks!
Best Regards,
Chris Büttner
July 11, 2008 at 6:25 am
Grant Fritchey (7/10/2008)
There's an "Anti-Rbar Alliance"?How do you join? ...
Uhhhm, .. do you want it in SQL-92 syntax or SQL-89 syntax?
😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 6:37 am
Hi Grant,
I don't think you should give up so easily because I posted a few months ago about ON and WHERE clause and the consensus was that it can makea difference and that I should always test with both if I have performance issues.
ALSO, if you use Left joins the where and on clause choice could drastically change your result set...
Could I also get the SQL-92 code for Join Anti-RBAR
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 11, 2008 at 6:38 am
Christian Buettner (7/11/2008)
Hi Grant,what makes you sure this time that it does not make a difference?
Did you get this information from Microsoft ?
I am pretty sure that I had examples in the past where I had differences, especially with complex queries.
But I cannot say for sure that I did not mess up one of the queries when moving the condition around.
If you have some evidence or explanation as to why this could not happen, it would be great if you could let me know.
Thanks!
I thought I had evidence to the contrary and I just can't find it. So, sans evidence, rather than disagree, I'm agreeing and apologizing for muddying the waters.
"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
July 11, 2008 at 6:39 am
rbarryyoung (7/11/2008)
Grant Fritchey (7/10/2008)
There's an "Anti-Rbar Alliance"?How do you join? ...
Uhhhm, .. do you want it in SQL-92 syntax or SQL-89 syntax?
😀
You know, either way works for me, just put it into a cursor.
"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
July 11, 2008 at 6:42 am
Hi Grant,
that sounds reasonable. I just wanted to know whether you had some more information than I do.
Thanks for letting me know.
I hereby apologize as well, but I will give it a few more tries to again find an example, should there exist one;-).
Best Regards,
Chris Büttner
July 11, 2008 at 6:57 am
HI All,
Here is a little test I have just done.
SELECT TOP 1000000 *
FROM CTSTest a
INNER JOIN CTSTest b
ON b.RowNum = a.RowNum
SELECT TOP 1000000 *
FROM CTSTest a,CTSTest b
WHERE b.RowNum = a.RowNum
Results respectively:
(1000000 row(s) affected)
Table 'CTSTest'. Scan count 2, logical reads 33402, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3656 ms, elapsed time = 81838 ms.
(1000000 row(s) affected)
Table 'CTSTest'. Scan count 2, logical reads 33402, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3875 ms, elapsed time = 81142 ms.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 11, 2008 at 7:28 am
What happens if you execute the queries in the opposite order?
Best Regards,
Chris Büttner
July 11, 2008 at 8:00 am
OK ...
I'm getting confused now, the results change all the time, sometimes the one is faster the sometimes the other one 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 11, 2008 at 8:03 am
The time differences on those are pretty trivial. The same number of scans and the same number of reads usually tell you most of what you need to know.
"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
July 11, 2008 at 8:55 am
Grant Fritchey (7/11/2008)
rbarryyoung (7/11/2008)
Grant Fritchey (7/10/2008)
There's an "Anti-Rbar Alliance"?How do you join? ...
Uhhhm, .. do you want it in SQL-92 syntax or SQL-89 syntax?
😀
You know, either way works for me, just put it into a cursor.
You fail... :p
I believe Jeff is open to anybody who fights against RBAR joining...
July 11, 2008 at 9:24 am
mtassin (7/11/2008)
Grant Fritchey (7/11/2008)
rbarryyoung (7/11/2008)
Grant Fritchey (7/10/2008)
There's an "Anti-Rbar Alliance"?How do you join? ...
Uhhhm, .. do you want it in SQL-92 syntax or SQL-89 syntax?
😀
You know, either way works for me, just put it into a cursor.
You fail... :p
I believe Jeff is open to anybody who fights against RBAR joining...
A WHILE LOOP then?
"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
July 11, 2008 at 9:27 am
Grant Fritchey (7/11/2008)
mtassin (7/11/2008)
Grant Fritchey (7/11/2008)
rbarryyoung (7/11/2008)
Grant Fritchey (7/10/2008)
There's an "Anti-Rbar Alliance"?How do you join? ...
Uhhhm, .. do you want it in SQL-92 syntax or SQL-89 syntax?
😀
You know, either way works for me, just put it into a cursor.
You fail... :p
I believe Jeff is open to anybody who fights against RBAR joining...
A WHILE LOOP then?
yer funny 😛
July 11, 2008 at 9:32 am
And if you try to next a subquery in the select portion of a query
i.e.
SELECT
howtojoin = (SELECT joinrequirements FROM requirements_table)
I will shoot you... 😛
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply