October 17, 2014 at 1:21 pm
I am reviewing a query that has case statements in it to replace codes with word values. (There are only three different codes and then an else.)
I believe I once heard someone say that case statements can cause poor performance and so I was going to create a reference table to retrieve these word values from instead. Unfortunately using profiler I do not have enough test data to confirm/deny my theory. (My results are soo similar right now.)
In general, it is a good idea to create reference/lookup tables instead of using case statements for gains in performance? I think I'm going to create the table anyway because it might be useful to another developer later on, but didn't know if I was also going to get a gain in performance down the road as a bonus or not.
October 17, 2014 at 3:03 pm
While this doesn't necessarily mean it isn't true, I have not heard of case statements causing a degradation in performance. If anything, I would think a case statement would probably work marginally better under some circumstances because it wouldn't have to do (presumably) a nested loop join to the reference table. All the work would be done by the database application on the row rather than having to reference an external value. If the size of your data is large enough that you're concerned about which would perform better, that may be a material fact.
If you ran a profiler trace and couldn't notice any difference between the two methods because the data set isn't large enough to cause any problems, then I think you have your answer right there: pick whichever is cleaner.
IMHO, case statements are best if you don't plan to re-use the mappings. For instance, if you have an output procedure and you want to replace everything with a code of "SC" with "Small Cap" or something. If you plan to reuse it in multiple places, or the mapping of the values is important for control of logic in an application, saving that down to a persisted reference table may be the way to go.
October 17, 2014 at 3:54 pm
A case statement in the SELECT list that just picks a value based on simple input shouldn't negatively impact performance. It's when you start putting all sorts of additional data retrieval mechanisms in that you run into issues with them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2014 at 4:09 pm
In general, it is a good idea to create reference/lookup tables instead of using case statements for gains in performance?
In general, for a limited number of values (say 10 or fewer), from a purely performance perspective, I would expect a properly written CASE to perform slightly better, as no I/O is required.
However, the more distinct lookup values you get, the more overhead there would generally be in a CASE.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply