Difference Between CASE and IF
-
I'm trying to improve performance on a very large stored procedure that takes about 30 seconds or so to run. I know there are larger problems I need to deal wtih, but I'm trying to grab some low hangin fruit here and was wondering about the difference in how SQL server handles IF logic Vs CASE WHEN THEN END logic.
The sp basically contains a whole lot of
IF @myVar = 'A'
BEGIN
INSERT INTO myTbl (col1) VALUES ('AA')
END
IF @myVar = 'B'
BEGIN
INSERT INTO myTbl (col1) VALUES ('BB')
END
I'd replace that logic with
INSERT INTO myTbl (col1) VALUES (CASE WHEN @myVar = 'A' THEN 'AA' ELSE 'BB' END )
I'm trying to come up with a good test scenario to figure which is faster and am somewhat at a loss. Nothing I can come up with to test it would be a large enough dataset to begin to show differences. I've tried looking at the results from Showplan_all, and it seems to be fairly comparable. I would seem to me that the Case would be faster because it's one less statement to parse but...
If anyone has any ideas on how I can see if one is faster takes less CPU/Memory IO etc I'm all ears.
Thanks.
-Luke.
-
The two statements do not do the same thing. The first tests the value twice, the second tests it once.
If both statements were written to achieve the same thing I doubt that there would be a performance gain - if the same logic is expressed using the simplest expressions, it is likely to run identically (and in a programming language the compiler would generate identical code).
If you want to compare like with like, you need to recast (1) as if 'A'... else... or (2) as case when 'A' then..., case when 'B' then...
-
I guess that's what I'm trying to get at... There will only ever be 1 of 2 values passed into this stored procedure because of validation that is happening in the application, It will always be either an A or a B, not sure why the folks that wrote that app didn't just use a boolean value or an int 0/1 type of thing, but hey... I just work here...
Anyhow, that's why I was thinking that the second statement would be slightly faster, because it's only testing the value once. Plus, to me it's more readable. Why make me read 2 statements when I can just read 1 with a case statement, but I digress...
Thanks.
-Luke.
-
Have you considered...Insert tablename(col1) values (@myvar + @myvar). Why even do the comparison?
-
SET @myVar = 'A'
INSERT INTO myTbl (col1) VALUES (CASE WHEN @myVar = 'A' THEN 'AA' ELSE 'BB' END )
CPU: 0 Reads: 20 Writes: 0 Duration: 0
I have made little change here ie. added just else codition.
SET @myVar = 'B'
IF @myVar = 'A'
BEGIN
INSERT INTO myTbl (col1) VALUES ('AA')
END
ELSE IF @myVar = 'B'
BEGIN
INSERT INTO myTbl (col1) VALUES ('BB')
END
CPU: 0 Reads: 3 Writes: 0 Duration: 0
So think instead of Case I would prefer IF ....ELSE
Thanks
Vitthal Shinde
-
I can pretty much GUARANTEE that futzing around with differences between IFs and CASEs you presented will be meaningless for improving performance. Those are CPU issues and thus have a miniscule effect on the rest of the sproc - even without seeing a single line of the remainder of the code. I/O (especially if it is physical disk I/O) is almost certainly the cause of the sproc running 30secs. Reduce/eliminate/speed I/Os and you will get your performance improvement.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Yeah I've already trimmed it down below 3 secs which is more than acceptable, but I'm just looking to see if I can do better than that. Also, it's more of trying to understand how the engine would handle this so that I can apply it better down the road. That whole bettering my personal understanding and ability to troubleshoot/tune things in the future bit.
Also I'm going for more readable code.
-
Even if you had 1000 of these IFs/CASEs in your sproc, I would not expect more than a millisecond or two difference between the two versions. You are talking registers in the CPU and stack pops/pushes, which at the Gigahertz timeframe of a CPU happen REALLY quickly!! 🙂
Congrats on the 10 fold performance increase, BTW! Bet it was I/O reduction, wasn't it??
That truly is the path to investigate deeply to "better your personal understanding and ability to troubleshoot/tune things..."!Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Another thing I think you overlookwed was with the case if @MyVar was some other value you will still issue and insert of 'BB' which you may not want to do. You might want to even combine logic to simplify and reproduce 1 for 1.
IF @MyVar IN ('A','B')
BEGIN
INSERT .... -- Case version
END
-
Yeah I'd thought about that, but that application already handles that logic, before it sets up the values it sends over to the stored procedure. I know I should re-validate the incoming data, and was planning to implement that and throw some sort of error if it's not a or b, but I was more concerned with processing time down to a reasonable level first.
As for why the 10 fold increase in performance, see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=366109
Yes it was a whole mess of IO that didn't have any reason for occurring.
In short the developers who wrote this application hadn't updated either their skill sets or habits from coding in the SQL 7 world even though it went live on a SQL 2k server in 02.
I still am doing some testing and believe that I'll be able to get it down under 1 or perhaps 1.5 seconds once all of the cursors and row by row processing are gone. It's all coming down to the previous dba not caring once it got pushed into prod. I'm having to rewrite thousands and thousands of lines of code to secure and improve the performance of this app.
-
>>As for why the 10 fold increase in performance, see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=366109 Yes it was a whole mess of IO that didn't have any reason for occurring.
I knew it!!
>>I'm having to rewrite thousands and thousands of lines of code to secure and improve the performance of this app.
Need any help?? My rate is quite reasonable!! LOL
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply