Forum Discussion

worthywow1988's avatar
worthywow1988
Frequent Visitor
1 year ago
Solved

Slow Switch Performance

Hi!

 

I have a switch statement to select a measure depending on the level the user wants to view. The code is similar to this:

 

Selected View = switch(TABLE_VIEW [Selected View],0,[Measure0],1,[Measure1],2,[Measure2],3,[Measure3],4,[Measure4],5,[Measure5])

 

 

It was running slowly (~10 seconds) so I did some experimenting. If I do the following it runs relatively quickly (~1.5 seconds)

 

Selected View = [Measure1]
Selected View = switch(TABLE_VIEW [Selected View],1,[Measure1])

 

 

The speed was roughly pro rata with the number of switch conditions so it seems to be the case that all the options in SWITCH are calculated regardless of if the test is true or false - is this as expected?

 

Is there any logic/code I can use so it only ends up calculating the selected/required measure?

 

Thanks!

 

 

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    worthywow1988 Put your most frequent values first. The SWITCH stops evaluating once a condition evaluates to TRUE. So, for example, let's say that 50% of your cases are 5, you would want that to be the first evaulation condition. Then if your next highest is 3, then that should be the second, and so on.

  • worthywow1988's avatar
    worthywow1988
    Frequent Visitor
    1 year ago

    Hi Greg - thanks for your reply. I thought that would be the behaviour but it seems to be doing something different i.e. if [Selected View] is, say 1, then

     

    Selected View = switch(TABLE_VIEW [Selected View],1,[Measure1],2,[Measure2],3,[Measure3],4,[Measure4],5,[Measure5])

     

    takes much longer to run then the below

     

    Selected View = switch(TABLE_VIEW [Selected View],1,[Measure1])

     

    when it theory it should take the same time?!