Clarity vs Speed
-
The following all produce the same results. Which would you use in your production code and why
a) a series of If statements
if @gross_pay < 35.17
select @boo = 0
else if @gross_pay >= 35.17 and @gross_pay < 151
select @boo = 40
else if @gross_pay >= 151 and @gross_pay < 201
select @boo = 50
else if @gross_pay >= 201 and @gross_pay < 251
select @boo = 60
else if @gross_pay >= 251 and @gross_pay < 301
select @boo = 70
else if @gross_pay >= 301 and @gross_pay < 351
select @boo = 80
else if @gross_pay >= 351
select @boo = 90
b) a function that calls the same series of if statements in "a)"
SET @boo = dboMaxTempDeduction(@gross_pay)
c) a Select statement
SET @boo = (SELECT 40 WHERE Exists(SELECT 1 WHERE @Inc > 32.5)) + isnull((SELECT ((convert(int, (@inc/50)) -3) * 10) + 10 WHERE EXISTS( SELECT 1 WHERE @inc > 150.01)),0) - isnull((SELECT ((convert(int, ((400.01 - @inc)/50)) -1) * -10) WHERE EXISTS( SELECT 1 WHERE @inc > 400)),0)
The speed of execution for each of these in microseconds is is a) 4.86 b) 51.64 c) 8.26
-
To be honest I wouldn't use any, instead I'd use a range table such as below, avoids hardwiring values
create table Ranges(GrossPayMin decimal(10,3) not null,
GrossPayMax decimal(10,3) not null,
Boo int not null,
primary key(GrossPayMin,GrossPayMax))
insert into Ranges(GrossPayMin,GrossPayMax,Boo) values(0, 35.17,0)
insert into Ranges(GrossPayMin,GrossPayMax,Boo) values(35.17,151,40)
insert into Ranges(GrossPayMin,GrossPayMax,Boo) values(151,201,50)
...
insert into Ranges(GrossPayMin,GrossPayMax,Boo) values(351,99999,90)
select @boo=Boo
from Ranges
where @gross_pay>=GrossPayMin
and @gross_pay<GrossPayMax
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Agreed. This is another perfectly viable method. Putting it through the same Iteration test it comes out as the third slowest (23 microseconds per iteration). If having the data matrix dynamic is a requirement to the problem then it is the ONLY reasonable solution.
-
Is it faster if a temp variable is used instead of a temp table?
How about if the table was pre-created? The dynamic solutions are still hardcoding values, to be truly dynamic they would be in an existing lookup table and there wouldn't be any insert overhead.
-
Actually I might do this personnally
SET @boo = (CASE
WHEN @gross_pay < 35.17 THEN 0
WHEN @gross_pay < 151 THEN 40
WHEN @gross_pay < 201 THEN 50
WHEN @gross_pay < 251 THEN 60
WHEN @gross_pay < 301 THEN 70
WHEN @gross_pay < 351 THEN 80
ELSE 90
END)
-
Sorry, it has both clarity and should have the speed you want.
-
I have tried the more dynamic lookup suggested earlier with both a fixed table and the table variable. Interestingly enough there is not a significant difference in the timing.
For raw speed the verbose If statements wins over everything. The more convoluted select statement is next, followed by the Select then the function call.
A side note: I was challenged to come up with my own version of the type of "Max" function that SQL lacks - the max between two variables - and this employs that methodology. There are few places one needs it but if you do this is as fast as it is ugly.
-
That is an excellent solution!
-
Heh... no matter how you swing it, this whole thing is RBAR... if it's for a GUI single row proc... no problem... if it even comes near a batch, big problem.
--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) -
What you are saying is true. In the real world where this code was snatched from with a little bit of salty refactoring I avoided this process entirely. Goes back to an old axiom "If you don't like the answer - rethink the question." That is off subject though.
I was very interested in where experienced, practical professionals would come down on the question of having to choose between "verbose maintainable and slow" vs "nicely black box but very slow" vs "efficient but not so obvious". I wanted to use some sample code that would demonstrate these notions. I fear I went afoul there. My apologies if I wasted anyones time.
There are soapboxes everywhere but given a sort of "Sophies Choice" of code I wondered which side serious professionals would come down on. As a person who manages, trains or mentors people on a regular basis I keep trying to understand development choices others make as a part of training my thinking. For various reasons that responsibility brought me to where I had a need to visit the "clarity vs Speed" issue.
Thank you to all of you who responded. Each and every response was very instructional.
-
Heh... ok... guess I'll get up on the soap box, then...
ALWAYS use the fastest, most effecient code possible... ALWAYS use comments to explain the code unless it's just too, too obvious. ALWAYS no problems that way 😉
Now, Duck... here come the "good enough" geese :hehe:
--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) -
Giving away my hand - I will always lean towards efficiency. I don't understand the phrase good enough. That seems self-defeating. You are knowingly leaving in errors. That's like leaving a little gravel (I had something more colorful in mine but this is a family program) in your bologna sandwich. I have been criticized for putting in-line assembly in my C++ code (well documented mind you) even though I could prove it was the only way to make a particular function work properly and stable.
I have a friend who likes to ask the question whether or not someone is "qualified to have an opinion." Here in the northern US, I have been often reprimanded "not to make it too complicated for other programmers." This always baffled me. So, I should reduce the4 effectiveness of my product so someone who probably shouldn't be working on it anyway can understand it ("Someone not qualified to have an opinion"). This was especially true when I was leading design and proto-typing in the area of configuration and AI.
I am still baffled.
-
My stance is always function over form with code. But sometimes what works best for one thing may require a different approach for another so understanding how things wokr and alternates ways of doing the same thing can pay off in the long wrong. The rest then is experimentation to resolve the best choice of 2 or more options.
Example, I once wrote and elegant query against an Oracle database that was easy to read and based on the normal design. However it took 43 minutes to complete digging thru the data andpresenting my requirements. Upon experimenting with the query plan stuff I found it kept using an index that really had poor bearing on the query as a whole but there was no direct way to prevent the choice. Started reviewing the data and decided as a long shot to try using GROUP BY with a HAVING clause for the piece I needed but was the root of the issue I was having. I found I got the same results in less than 3 minutes every time. So even beyond basic working function there can be options we need to look for to improve the overall expierence.
-
Now, that's what I'm talking about... 43 minutues vs 3... both have the correct answers... which one do you want running on your machine? 😉
--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) -
If you want speed, try this. It minimises the number of comparissons - it should be quicker than the others.
SELECT @boo =
( CASE WHEN @gross_pay < 151 THEN
CASE WHEN @gross_pay < 35.17 THEN 0 ELSE 40 END
WHEN @gross_pay < 251 THEN
CASE WHEN @gross_pay < 201 THEN 50 ELSE 60 END
WHEN @gross_pay < 351 THEN
CASE WHEN @gross_pay < 301 THEN 70 ELSE 80 END
ELSE 90
END
)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply