For years, I told people to avoid iterators. I compared them to cursors in SQL, which are really bad, or for loops in C# which are normally fine. I knew that DAX was column based and that it often broke down when doing row-based operations, but I couldn’t tell you why.

Advice to avoid iterators is often based on a misunderstanding and a misapprehension of how the Vertipaq engine works. If you are blindly giving this advice out, like I was, you are promoting a fundamental misunderstanding of how DAX works. We think that they are running row-by-agonizing-row (RBAR). Toiling away and wasting CPU.

The truth is that SUM and SUMX are the same. Specifically, SUM is syntactic sugar for SUMX. That means when you write SUM, the engine functionally rewrites it as a SUMX. There is no performance difference. There is no execution difference. There are identical execution plans. You can look for yourself.

Looking at the data

Here is the evaluation of SUM over 100 million rows of Contoso generated data, gathered with DAX Studio. With caching off, it takes 13 milliseconds and performs a single scan operation.

Here is SUMX over the same data. 15 ms, same scan operation, same xm_SQL output on the right. Any DAX within 4ms should be considered to have functionally identical performance, according to SQLBI.

Here are the physical and logical execution plans for SUM:

Here are the logical and physical plans for SUMX. Identical.

Why the confusion?

So why is this a point of confusion? It is good to avoid row-based operations in general, but the engine often optimizes those away behind the scenes. So a blanket ban on SUM is silly and misguided.

The fact of the matter is that if you stick to functions like SUM then you will fall into the pit of success. You will have better performance, on average, because the code you write will better align with how the formula engine and the storage engine work. CALCULATE + SUM is like having a safety on your code and when you have to step outside of that and use iterators like SUMX or FILTER you know that you have to be more cautious.

Sticking to SUM will force you to engage in patterns that often lead to better performance. But SUM by itself makes no difference.

But beyond that, it’s easy to write really, really bad code with iterators. If you put an IF statement inside of your SUMX then you will see CALLBACKDATAID, which is a sign the storage engine is having to make calls to the formula engine to handle logic it can’t handle by itself. Depending on how poorly you write your SUMX, it may do the vast majority of the work in the formula engine instead of using the storage engine and sending back data caches.

If you want to learn more, I recommend checking out the super comprehensive book by SQLBI or my course on performance tuning.