October 26, 2011 at 11:32 am
QUESTION: If you have an SP that takes in a number and then returns a value and the contents of the SP does not read from any table but only compares the value passsed in to one of many possibles values (after a formula is applied to the value passed to the SP) then is it more effecient to go with a CASE statement (example #1 below) or an IF THEN (example 2) or is it irrelevant since there's no tables involved?
ADDITIONAL INFO: This is not code of my own making but part of the stoock SP's in a database one of our systems uses and it seems to me like the thing was done by a programmer and not a DB/DBA type since it looks more like VB code then T-SQL. I can't chjange it simply because I want to but if I can make the case that it would be more effecient (to use) if it were changed to use the Case statement (since testing in a Case stops when you hit a TRUE where as the IT-Then versions perfrorms all tests even if the first one is true) then I could get teh vendor to do this.
BTW - There are many more levels of testing teh number I just reduced it to 4 levels or tests here to avoid being excessive.
Thoughts?
Example #1: CASE ExampleCREATE PROCEDURE [dbo].[what_is_my_base_value_CASE] (@MyNumber NUMERIC, @MyNumbersBaseValue NUMERIC OUTPUT)
AS
DECLARE @iBaseType NUMERIC
BEGIN
SET @iBaseType = floor(IsNull(@MyNumber,0)/10000000000)
SELECT @iBaseType = Case WHEN @iBaseType = 33 THEN 3
WHEN @iBaseType = 36 THEN 6
WHEN @iBaseType = 39 THEN 10
WHEN @iBaseType = 48 THEN 7
WHEN @iBaseType NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 33, 36, 39, 48, 18, 19 ) THEN 0
ELSE @iBaseType
END
SET @MyNumbersBaseValue = @iBaseType
END
Example #2 - IF-THEN Example
CREATE PROCEDURE [dbo].[what_is_my_base_value_IFTHEN] (@MyNumber NUMERIC, @MyNumbersBaseValue NUMERIC OUTPUT)
AS
DECLARE @iBaseType NUMERIC
BEGIN
SET @iBaseType = floor(IsNull(@MyNumber,0)/10000000000)
IF @iBaseType = 33
SET @iBaseType = 3
IF @iBaseType = 36
SET @iBaseType = 6
IF @iBaseType = 39
SET @iBaseType = 10
IF @iBaseType = 48
SET @iBaseType = 7
IF NOT @iBaseType IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 33, 36, 39, 48, 18, 19 )
SET @iBaseType = 0
SET @MyNumbersBaseValue = @iBaseType
END
Kindest Regards,
Just say No to Facebook!October 26, 2011 at 11:40 am
Without tables involved, it's procedural code either way you slice it.
I find the Case version more readable, but that's a personal opinion, not something that can be backed up with facts or studies.
- 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
October 26, 2011 at 11:41 am
Case will short-circuit. So with the current code should be faster.
However if you were to write
if
else if
else if...
It should be equal.
October 26, 2011 at 11:44 am
I prefer the case for many scenarios due to readability.
However, if the "if..else..else" kind of logic is used, readability becomes equal to me and thus either would be fine.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 26, 2011 at 11:51 am
The replies so far are what I expected. Based on that I have a follow-up question.
Does this approach/design method seem highly inefficient and therefore a poor choice within a RDBMS environment when one could more efficiently do the same thing by using a lookup table that would store these correlating values and then join it to the main query instead of calling an SP for each number you want to get a base for?
I can't post the actual real code due to an NDA but in summary what is happening is there’s a cursor (man I hate those things) that goes thru a set of rows and calls this SP ( the one using the IF-THEN construct) to get a base value and then does some work with the data in that row and then moves to the next row. I would think it would be far more efficient to join the main query to a lookup table to get the base number even if you still are using the cursor simply because it avoids a lot of calls to a stored procedure.
Thoughts?
BTW - Thanks for replying so quickly
Kindest Regards,
Just say No to Facebook!October 26, 2011 at 12:14 pm
Lookup table will definitely be faster and more efficient.
- 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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply