Slow Switch Performance

[worthywow1988]

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!

[worthywow1988]

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?!

[Greg_Deckler]

@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.