Simple vs Searched CASE statements

I recently had a customer show me an MDX calculation using a CASE statement, where they had achieved a big improvement in performance when they moved from using a searched CASE to using a simple CASE. For the record, here’s an example of a simple CASE statement:

CASE [Measures].[Internet Sales Amount]

And what the equivalent searched CASE looks like this:

WHEN [Measures].[Internet Sales Amount]=0 THEN 0
WHEN [Measures].[Internet Sales Amount]=1 THEN 1
WHEN [Measures].[Internet Sales Amount]=2 THEN 2
WHEN [Measures].[Internet Sales Amount]=3 THEN 3

In my customer’s code they were doing lots of complex conditions involving calculated measures, but nonetheless I couldn’t work out why their searched CASE performed so much worse because the logic in both the simple and the searched CASE was the same. Therefore, in turn I asked my friends in Redmond and Marius Dumitru gave me some very useful insights that I thought I’d pass on. Basically, a searched CASE requires all previous branch conditions to have been evaluated before a given branch is chosen, which in turn makes it harder for the query optimiser to evaluate a searched CASE in bulk mode. As a result it’s always better to use a simple CASE where possible; in the worst scenarios (and I guess this is what was happening to my customer) the performance of a searched CASE can be several times worse than a simple CASE even when they seem to be doing the same thing.

5 responses

  1. Hi Chris,What about using a nested IIF statement? Which approach would be the fastest?I always thought using IIF\’s would be the fastest..–Jorg Klein

  2. I think a nested IIF is easier to optimise (especially if you use IIF query hints), but it won\’t necessarily perform better than a CASE statement.

  3. Hi Chirs,I made a test and found there is no difference in Excution plan, I think if you use the same logic to calculate with simple case statement and search statement, you will spend the same time.Please see my test code:DROP TABLE dbo.NumsGO–================================================================–CREATE TABLECREATE TABLE dbo.Nums( ID INT)GO–================================================================–padding dataDECLARE @num INTSET @num =1WHILE @num<1000BEGININSERT INTO dbo.Nums(ID)VALUES(@num)SET @num=@num+1ENDGODECLARE @max INTSELECT @max = MAX(Id) FROM dbo.NumsWHILE @max<1000000BEGINSELECT @max = MAX(Id) FROM dbo.NumsINSERT INTO dbo.Nums(ID)SELECT ID+@max FROM dbo.NumsENDGO–================================================================–Test–SIMPLE CASE STATEMENTSELECT CASE ID%10 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 ELSE 0 ENDFROM dbo.Nums–SEARCH CASE STATEMENT SELECT CASE WHEN ID%10=1 THEN 1 WHEN ID%10=2 THEN 2 WHEN ID%10=3 THEN 3 WHEN ID%10=4 THEN 4 WHEN ID%10=5 THEN 5 WHEN ID%10=6 THEN 6 WHEN ID%10=7 THEN 7 WHEN ID%10=8 THEN 8 WHEN ID%10=9 THEN 9 ELSE 0 ENDFROM dbo.NumsGO

  4. Sorry, I should have pointed out that this is in MDX and not SQL… And in any case, you won\’t see a difference in the query plans in all cases in MDX, it\’s just that in general the simple case is more likely to perform better.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: