- Ciao, friends.
Using DAX, you can divide two numbers
using two different techniques.
You can use the regular slash operator
that divides two numbers
or you can use the DIVIDE function
in case the denominator might be zero.
Indeed, DIVIDE returns infinity
if you try to do a division by zero,
whereas DIVIDE always returns its third argument,
which defaults to a blank.
Now, whenever you have the option of doing something
in two different ways, the natural question
is which one is the best technique?
You can find a lot of different answers.
Somebody tells you that you always need
to use DIVIDE no matter what.
Somebody else says no, division is better.
In this video, I want to show you which one is the best
and we will do that by analyzing the query plan
and understanding exactly the difference
between DIVIDE as a function or the divide operator.
Let's go straight to the PC and look at a demo.
I have my regular Contoso model here
with 12 million rows.
And we want to evaluate the performance of DIVIDE.
So we start by defining a measure.
Let's write DEFINE MEASURE sales divide one.
And divide one is just an average
because we want some aggregator.
We iterate over sales and we divide,
let's take, for example, the average discount percentage.
So we take the average unit discount
divided by the sales unit price.
And finally, we need to return a number.
So we just compute div one.
Now, we want to evaluate the performance of this measure.
So we need to enable the query plan
and then look exactly at the time it takes.
Let's do that.
We want the server timings, not the query plan.
Let's wait for a while until it starts.
And then we run the query.
It was very fast, let's zoom in a bit
and also go in bottom layout.
You see that the entire query took 29 milliseconds
and 94 milliseconds of storage engine.
Everything is running inside storage engine
and the entire calculation is carried on by storage engine.
The xmSQL query that is executed,
CAST the discount AS INT and then divides it by 10,000
because it's a currency.
So all the math need to be done with four decimals
and it's just computing the SUM
of the expression where expression is the division
of unit discount by unit price.
So the division has been executed by storage engine
and it took 29 milliseconds.
Let's run it a couple of times just to have an average.
29 and 19, 22, 21.
Let's take this value.
So if we just carry on a regular division,
we take 21 milliseconds of total execution time
and most important, 94 milliseconds
of storage engine CPU.
Always remember that the storage engine CPU
is the time needed in order to compute a number,
whereas the execution time takes into account parallelism.
So the two numbers are relevant.
And you need to compare both in order
to understand which one is faster
from the user perspective
and from the CPU usage point of view.
Now, this calculation would fail
in case unit price is equal to zero.
So we need to protect this code.
And we can actually write a different version
of the same code,
it messed up everything, where we check for the denominator.
Let's write divide two.
We still don't know the times,
where we add an IF statement,
saying well, if the unit price is different than zero,
then do the division, otherwise,
so if this is different than zero, do the division,
otherwise return a blank.
We need a bit more space here.
So divide two iterates over sales
and now it introduces an IF statement.
Is it gonna be slower or faster?
We have the feeling that it will be slower
but we don't know how much slower.
So let's evaluate divide two,
run the query a couple of times.
It went in 53, 50, 67,
59 - okay, let's take 59 milliseconds.
469 milliseconds of
storage engine CPU, and the most relevant thing
is that if you now look at the xmSQL query
that is executed, we have a CallbackDataID.
Indeed, the IF statement cannot be executed
by storage engine, so formula engine is involved.
Still, the execution time went from 21 to 59 milliseconds.
So the calculation required a CallbackDataID.
CallbackDataID are bad but not all that bad.
They are kind of good
because parallelism is still involved.
And the formula engine executed the IF statement,
whereas storage engine is just scanning the data.
So from 21, we went to 59 milliseconds.
Now it's time to take a look at DIVIDE
because the IF statement protects us
from a division by zero but DIVIDE does the same.
So let's give DIVIDE a try by creating a third measure.
We copy the first one and we create
divide three where this time,
instead of doing the division,
we divide one by the other.
And then let's evaluate division three.
Let's run it to have an idea of the time it needed
and now it's 43 milliseconds,
297 milliseconds of storage engine CPU.
Let's run it a couple of times again.
Oh, that was slow.
That is probably better kind of timing.
49, 328.
So 49 milliseconds, 328 as the execution time.
And if we look at the xmSQL query,
you see that we still have a CallbackDataID
that this time evaluates DIVIDE.
So DIVIDE, like IF,
cannot be executed by the storage engine.
It requires formula engine,
so we still have a CallbackDataID.
Performance is a bit better
if you compare it with the IF statement
but it is worse than a regular division.
So can we do anything better than that?
Actually, we can because if we guarantee
that the denominator will never be zero,
we can use a regular division
and we can guarantee that the denominator
will never be zero if we just removes zeros
from the entire calculation.
So we run a CALCULATE upfront
that gets rid of any denominator that is equal to zero
and then we run a straight division.
Let's do that.
We can create a fourth version of the code.
Let's copy again the first one.
And we create divide four.
No, divide four.
We use a regular division but this time,
we embed it inside CALCULATE
and we check that the denominator
need to be different than zero.
Different than zero.
Than zero.
Okay, how is divide four going to perform?
We don't know that but we can test it.
We run it once and it looks faster.
We run it twice.
Three times. That is kind of consistent.
26.
172.
And most relevant, if we look at the query plan,
you see that there are no CallbackDataID.
The entire calculation has been pushed down
to the storage engine.
So let's draw some conclusion from the numbers we have seen.
21 milliseconds is the time for a regular division.
59 if we use the IF statement.
49 if we use AVERAGEX over DIVIDE
and 26 if we use CALCULATE to perform the division.
So does that mean that DIVIDE
is slower than a regular division?
Yes, it is.
But it is also important to look at the code
and understand exactly how much slower
because we went from 29 milliseconds to 49 milliseconds.
The difference is really tiny
and besides, it's very unlikely
that your code only contains one division.
So an AVERAGEX over a division as I'm doing here.
It is much more likely that your code is much more complex.
So the presence of CallbackDataID is absolutely normal.
And the difference of a few milliseconds
in a division is not really relevant.
If you can use CALCULATE, then go for that
because that is gonna provide the best performance.
But when the code is more complex,
then the choice is no longer so simple.
For sure, a division is faster but DIVIDE works just fine.
So which one is better?
You know the answer. It depends.
If you are really, really striving for perfection
in order to find the best possible performance,
then a regular division is the way to go.
But DIVIDE is not that bad.
So I would not be worried at all.
The difference between DIVIDE and division
is by far not the first thing that I look at
when I need to optimize code written by anybody.
Enjoy DAX.