What can we do to make queries as fast as possible? Here is some tips 🙂

In my simple test I want to see the sales for year 2009. To make sure my queries does not hit any cache I’m clearing it using XMLA. In my first query I will try to use the ROW function and sum up the internet sales

EVALUATE
ROW (
"Sales", CALCULATE (
SUM ( 'Internet Sales'[Sales Amount] ),
'Date'[Calendar Year] = 2008
)
)

When watching the profiler I can see that this query took 156 milliseconds. The reason why this takes quite long is that we are one the row level.

Lets switch to table level.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales", SUM ( 'Internet Sales'[Sales Amount] )
),
'Date'[Calendar Year] = 2008
)

This query took 78 milliseconds. Twice as fast as the first query.

What if I move the calculation using ADDCOLUMNS outside the SUMMARIZE?

EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE ( 'Date', 'Date'[Calendar Year] ),
'Date'[Calendar Year] = 2008
),
"Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)

This query took 62 milliseconds

Do you have an even better optimalization tip? Feel free to drop me an mail