Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Simple vs Searched CASE statements

with 5 comments

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]
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
ELSE 4
END

And what the equivalent searched CASE looks like this:

CASE
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
ELSE 4
END

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.

Written by Chris Webb

December 9, 2009 at 9:56 pm

Posted in MDX

5 Responses

Subscribe to comments with RSS.

  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

    Jorg

    December 11, 2009 at 4:09 pm

  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.

    Chris

    December 11, 2009 at 10:34 pm

  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

    Tey

    December 13, 2009 at 1:49 pm

  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.

    Chris

    December 13, 2009 at 7:04 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: