January 15, 2009 at 2:40 pm
ScottPletcher (1/15/2009)
Again, this code does work:select isdate(col1), *
from #t
where isdate(col1) = 0
or datepart(year, col1) = 2001 --sql treats yr "1" as "2001"
SQL will definitely short circuit when it knows it can.
Yep.
Edit: But that does bring to mind another very common mistake: Mixing OR and AND in such a way as to end up with wrong results, because of missing parens. May have already been mentioned, I don't remember, but it's a VERY common mistake in just about every programming language I've ever seen. (Not that you had that mistake. It just made me think of it, because of the differences between the two.)
- 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
January 15, 2009 at 2:44 pm
And using your code with the condition flipped returns the first row and then fails. This now tells me that with the OR condition, it goes left to right.
select isdate(Col1), *
from #T
where datepart(year, Col1) = 2001 --sql treats yr "1" as "2001"
or isdate(Col1) = 0
So if it short circuits when using an OR condition, why not an AND condition? Rhetorical, I don't expect you to answer that, it really is a question for Microsoft.
January 15, 2009 at 2:49 pm
Isn't the point of that whole debate that the optimizer can do what it wants? While the test code may demonstrate certain characteristics, is anyone certain that another query, possibly using a table with indexing or with more rows or other differences it wouldn't choose to go right to left?
January 15, 2009 at 2:53 pm
Garadin (1/15/2009)
Isn't the point of that whole debate that the optimizer can do what it wants? While the test code may demonstrate certain characteristics, is anyone certain that another query, possibly using a table with indexing or with more rows or other differences it wouldn't choose to go right to left?
Yes. The point is that it's a common mistake to assume that left-to-right matters at all in Where clauses.
- 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
January 15, 2009 at 3:02 pm
I'll take a break from gnashing my teeth & swearing at the original coder to add my common mistake:
Frequent use of DISTINCT to hide a fundamental design flaw and/or hide a lack of understanding of the data model & cardinality.
Now, back to the unbudgeted re-write.
January 15, 2009 at 3:08 pm
PW (1/15/2009)
I'll take a break from gnashing my teeth & swearing at the original coder to add my common mistake:Frequent use of DISTINCT to hide a fundamental design flaw and/or hide a lack of understanding of the data model & cardinality.
Now, back to the unbudgeted re-write.
Yep, that's one I see in some of the code here that pre-dates my arrival here at my organization.
January 15, 2009 at 3:11 pm
Excellent point about DISTINCT. I see it all the time.
Also, using HAVING to check for conditions that should be in a WHERE. That can have a serious impact on SQL's performance, too.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 15, 2009 at 3:13 pm
PW (1/15/2009)
I'll take a break from gnashing my teeth & swearing at the original coder to add my common mistake:Frequent use of DISTINCT to hide a fundamental design flaw and/or hide a lack of understanding of the data model & cardinality.
Now, back to the unbudgeted re-write.
I'll admit to being guilty of that one a few times when I started writing SQL. Nice addition.
January 15, 2009 at 3:17 pm
ScottPletcher (1/15/2009)
As I said, it was my understanding that the SQL optimizer did not do that for simple (non-query) expressions connected by boolean operators, that it evaluated left-to-right.
You may be thinking of operator evaluation for scalar expressions in the output columns, which SQL Server does little or no optimization with.
It certainly is not true for search expressions in my experience.
[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]
January 15, 2009 at 3:28 pm
ScottPletcher (1/15/2009)
Again, this code does work:select isdate(col1), *
from #t
where isdate(col1) = 0
or datepart(year, col1) = 2001 --sql treats yr "1" as "2001"
SQL will definitely short circuit when it knows it can.
No.
First, that is not short-circuiting which has to do with skipping certain sub-expressions in the evaluation of a single (larger) expression, it is just applying the search conditions. These are two different expressions: Each column of the SELECT list clause is a separate expression, but the whole WHERE clause is one single expression.
Secondly, the WHERE clause must be evaluated before the output columns. That's required by the ANSI SQL standards and SQL Server has been implemented and documented to conform to this as long as I have been using it (at least back to 6.5 or even 4.2).
[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]
January 15, 2009 at 3:32 pm
RBarryYoung (1/15/2009)
Secondly, the WHERE clause must be evaluated before the output columns. That's required by the ANSI SQL standards and SQL Server has been implemented and documented to conform to this as long as I have been using it (at least back to 6.5 or even 4.2).
Of course, I mean just this aspect. Obviously, SQL Server does not conform to the whole standard (I can dream).
[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]
January 15, 2009 at 6:36 pm
ScottPletcher (1/15/2009)
DB2 will sometimes even "rewrite" the conditions to something logically equivalent but faster.But it was my understanding that SQL Server did not do that, at least thru SQL 2005.
Is there evidence to the contrary?
Look at this in the execution plan...
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee
WHERE EmployeeID BETWEEN 100 AND 200
Notice how it rewrote the code? Is that what you're talking about?
--Jeff Moden
January 16, 2009 at 2:16 pm
how about multiple levels of view? we have tables created from 7 or 8 levels of views....
January 16, 2009 at 2:36 pm
Look at this in the execution plan...
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee
WHERE EmployeeID BETWEEN 100 AND 200
Notice how it rewrote the code? Is that what you're talking about?
No, not at all. It did not rewrite it, it "parameterized" it. It certainly didn't change a seq of comparions, since there wasn't a seq of comparisons in the orig query.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 14 posts - 61 through 73 (of 73 total)
You must be logged in to reply to this topic. Login to reply