May 20, 2009 at 9:44 am
Thanks everybody, I think everything is clearer now.
Short circuiting is a topic I've always found interesting, but neved had the time (or skills) to investigate deeply.
I think I can collect all the information in this thread and sum it up in an article: maybe some other members would be interested.
First of all I'll take the time to go through some deeper testing.
-- Gianluca Sartori
May 20, 2009 at 9:49 am
I agree, this was a very useful thread and I think alot things have been cleared up 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 2:26 pm
Barry and Gail, you are both right of course. I'm still stuck somewhere between my C++ background, where short-circuiting of boolean expressions is part of our thinking (explain that to a VB 6 programmer :-)), as opposed to the assumptions the SQL optimizer may need to make about all kinds of information it has as its disposal so as to come up with a most optimal query plan when faced with such an opportunity.
Well, even if disobeying those very basic logical rules, i.e. when one considers evaluating boolean expressions should be done from left to right, taking operator precedence into account, this is not how set based thinking works. Disobeyance of these rules may well result in a very sub-optimal plan where a human being can clearly see that the boolean expression, if short-circuited, will be more optimal. But after all, machines aren't yet human, luckily, and thankfully on the other hand we aren't machines yet either.
May 20, 2009 at 8:45 pm
By the way, here's how to manually code for short-circuiting:
, first the AND example:
-- Change this:
SELECT *
From syscolumns
Where IsNumeric(name) = 1
And Convert(Float, name) > 1.5
-- To this:
SELECT *
From syscolumns
Where Case When IsNumeric(name) = 1
Then Convert(Float, name) Else 0.0 End > 1.5
And the OR example:
-- Change this:
SELECT *
From syscolumns
Where IsNumeric(name) = 0
OR Convert(Float, name) > 1.5
-- To this:
SELECT *
From syscolumns
Where Case When IsNumeric(name) = 0 Then 2.0
Else Convert(Float, name) End > 1.5
[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]
May 21, 2009 at 12:13 am
Jan Van der Eecken (5/20/2009)
Barry and Gail, you are both right of course. I'm still stuck somewhere between my C++ background, where short-circuiting of boolean expressions is part of our thinking (explain that to a VB 6 programmer :-)), as opposed to the assumptions the SQL optimizer may need to make about all kinds of information it has as its disposal so as to come up with a most optimal query plan when faced with such an opportunity.
The biggest mistake I see people making w.r.t. short circuiting is assuming that because it happens in some circumstances, it happens in all circumstances.
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
May 21, 2009 at 9:35 am
ORIGINAL CODE:
select distinct @Result = sum( column1 )
from table (nolock)
where ((column2 = @param1) or (@param1 = 0))
1) why do you have distinct? you are doing a SUM, which can have only one output. distinct is redundant and wasteful
2) the correct way to do this query, which guarantees optimal query plan (caveat that parameter sniffing @param1 with nonzero values can still happen) for any @param1 input is this:
if @param1 = 0
begin
select @result = sum(column1)
from table (nolock)
--note NO where clause
end
else
begin
select @result = sum(column1)
from table (nolock)
where column2 = @param1
end
if you want to tweak every teesy bit of performance out have the IF clause set up to hit the most frequent situation first (i.e. @param1 0 if you expect nonzero values most of the time).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2009 at 10:13 am
Thinking on the performance side, I can only agree with you, but you can't forget that performance is just one of the qualitites of a good software: what about maintainability?
Do you really think that duplicating the base query inside an IF statement is the best possibile solution? In this case the statement is trivial, but what if it was a 10 tables join with complex filter conditions? You would have to maintain the two statements in the "then" and "else" side...
And this is just for a simple parameter: what if parameters were 10 or more? Should we code a single statement for all possibile combinations? And maintain them all???
I think that finding the right balance between performance and maintainability is a MUST and a good dba should not close his eyes and pretend that performance is everything, especially when developers claim their right to do their job at best.
If performance perceived by users is acceptable, even catch-all queries and other sort of poor performing statements are a legitimate solution, because they maximize maintainability.
Just a thought...
-- Gianluca Sartori
May 21, 2009 at 11:42 am
Gianluca Sartori (5/21/2009)
Thinking on the performance side, I can only agree with you, but you can't forget that performance is just one of the qualitites of a good software: what about maintainability?Do you really think that duplicating the base query inside an IF statement is the best possibile solution? In this case the statement is trivial, but what if it was a 10 tables join with complex filter conditions? You would have to maintain the two statements in the "then" and "else" side...
And this is just for a simple parameter: what if parameters were 10 or more? Should we code a single statement for all possibile combinations? And maintain them all???
I think that finding the right balance between performance and maintainability is a MUST and a good dba should not close his eyes and pretend that performance is everything, especially when developers claim their right to do their job at best.
If performance perceived by users is acceptable, even catch-all queries and other sort of poor performing statements are a legitimate solution, because they maximize maintainability.
Just a thought...
1) Please reread the OP. My post is a precise and correct response to his entire OP. Be sure to understand that the request was directly for help with PERFORMANCE and gave an explicit example.
2) I did not espouse the use of this for your "what ifs". I do not often waste my time coming up with such permutations as you did (or solving them) that are not relevant to the post. I haven't seen you on these forums before - perhaps you would benefit from focusing on the poster's (in this case clearly-stated) problem instead of postulating scenarios in an attempt to refute a valid and proper solution?
3) I also don't waste my time making statements in posts posts unless I "really think" that they are the "best possibile solution".
4) Your statement that "finding the right balance between performance and maintainability is a MUST" is limiting. The DBA/Dev has to determine their own priorities among those two (and many other) requirements given the current need. Sometimes shaving another 4ms off of a sproc runtime is all that matters. Sometimes LINQ-to-SQL is ok because it is a one user system with 5MB of total data in the database and the dev is a LINQ-to-SQL wiz.
5) Since you brought it up, the way that I have used a number of times to solve the "multiple NULLABLE input parameters" situation (which I find much more common than the use of a zero as a default), where the code would have lots of WHERE clauses such as this (and also unrequired joins, etc):
AND ((@param3 IS NULL) OR (@param3 = a.field3))
is to use dynamically generated sql, which again gets optimal plans and is easily maintained once set up. It can be a definitel PITA to get coded initially however! But, it leads to multiple orders of magnitude better performance as well as significant concurrency gains and has been used to fix essentially unusable systems. Obviously you must ensure you are protected against SQL Injection here.
6) I strongly disagree with your statement "If performance perceived by users is acceptable, even catch-all queries and other sort of poor performing statements are a legitimate solution, because they maximize maintainability.". I can't tell you the number of times I get called in to fix the "well it worked great in DEV" or "it was fast for the last 6 months and then it fell off a cliff" type problems. Hmm, come to think of it I withdraw my objection. I get paid a lot of money to clean up those messes that people like you who believe that statement wind up with - so by all means please continue to work that way!! 😎
Just a few thoughts . . .
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2009 at 1:01 am
I spent the last 10 minutes reading over and over my post, and I can't find the offending sentence that set you up that bad. Please note that English is not my native tongue, maybe in Italian it would have a different sound.
I'm a member since 2001 (with a different account I lost when I changed my job two years ago). If you didn't see me around, maybe it's because I don't post very often, I post only when I think I can contribute and help other members with the little knowledge I have. Sure they can live without my contribution, but I can't live without yours, so, if this doesn't make a problem to you, I'll keep on reading and posting, even if I'm no MVP or SQLGuru. Hope you don't mind.
If this isn't the right place to discuss strategies and share thoughts, please let me know and I'll unsubscribe. If you think I was unpolite, report the editor, but please note that it was not my intention.
-- Gianluca Sartori
May 22, 2009 at 3:14 am
TheSQLGuru (5/21/2009)
I do not often waste my time coming up with such permutations as you did (or solving them) that are not relevant to the post. I haven't seen you on these forums before - perhaps you would benefit from focusing on the poster's (in this case clearly-stated) problem instead of postulating scenarios in an attempt to refute a valid and proper solution?
Was that really called for?
I'm going to assume you didn't intend it as insulting, but it certainly can be read that way.
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
May 22, 2009 at 3:23 am
Gianluca Sartori (5/22/2009)
I'm a member since 2001
I have to correct myself, I found my old account and it's dated 2004.
-- Gianluca Sartori
May 22, 2009 at 3:29 am
I'm sorry but I have to back up Gianluca Sartori here.
He has tried to help the OP at the beginning of the thread with some suggestions on changing the order of the OR statement in the where, as was due the knowledge that he and I myself thought that perhaps short-circuiting the OR would solve the problem.
The OP mentioned that adding a thinner index solved the problem, however curiosity got the better of us, and we tried to find out more about Short-Circuiting to be sure that the ideas and solutions we gave helped, both the OP and future readers.
SSC is an open community of developers both expert and beginner, the point of places like this is to learn, sometimes you find you learn from what you post as a solution and sometimes you learn from what you ask as by posting a problem!
Remember there are many ways to skin a cat... I can't possible know if mine is the best until I have another way to compare it to...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 11:24 am
GilaMonster (5/22/2009)
TheSQLGuru (5/21/2009)
I do not often waste my time coming up with such permutations as you did (or solving them) that are not relevant to the post. I haven't seen you on these forums before - perhaps you would benefit from focusing on the poster's (in this case clearly-stated) problem instead of postulating scenarios in an attempt to refute a valid and proper solution?Was that really called for?
I'm going to assume you didn't intend it as insulting, but it certainly can be read that way.
I did not mean it as insulting but do see it could be read that way.
My apologies to Gianluca.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2009 at 1:33 am
TheSQLGuru (5/22/2009)
My apologies to Gianluca.
No problem. I have to thank you indeed, because you gave me the opportunity to raise one of the most challenging aspects of working as a dba today. I'd like to write on this topic again and share with the community and I look forward reading your opinion.
Gianluca
-- Gianluca Sartori
May 27, 2009 at 8:51 am
Greetings all
A couple of thoughts:
1.
Gianluca Sartori (5/20/2009)
Now I'm sure that SQLServer uses shortcircuit OR. Try this:
DECLARE @tmpTab TABLE (
column1 int,
column2 int
)
declare @param1 int
declare @result int
set @param1 = 3
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,0) -- This should generate a divide-by-zero error
select *
from @tmpTab
where column1 = column1
OR (column1/column2 = 1)
If the engine evaluated the second expression, this would cause a divide by zero error, which is not thrown.
Interesting, isn't it?
There's shouldn't be any need to validate the second part of the OR clause when the first condition has been met. I would be perplexed if this was the case and the second part of the OR clause would negate the true condition, but that's what the AND predicate is for.
2.
Gianluca Sartori (5/21/2009)
Thinking on the performance side, I can only agree with you, but you can't forget that performance is just one of the qualitites of a good software: what about maintainability?Do you really think that duplicating the base query inside an IF statement is the best possibile solution? In this case the statement is trivial, but what if it was a 10 tables join with complex filter conditions? You would have to maintain the two statements in the "then" and "else" side...
And this is just for a simple parameter: what if parameters were 10 or more? Should we code a single statement for all possibile combinations? And maintain them all???
I think that finding the right balance between performance and maintainability is a MUST and a good dba should not close his eyes and pretend that performance is everything, especially when developers claim their right to do their job at best.
If performance perceived by users is acceptable, even catch-all queries and other sort of poor performing statements are a legitimate solution, because they maximize maintainability.
Just a thought...
Sometimes it is the job of the dba to make sure that production servers run smoothly and that code being introduced to prod doesn't adversely effect the server or performance and they then have to re-write perfectly good code.
3. Gail, I think RECOMPILE was available in 2005 and it would have been possible in 2k to use: CREATE PROC dbo.sp_TestProc @intPara1
WITH RECOMPILE
AS...
Max
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply