- Ciao friends.
In this video, I want to show you
how the correct use of variables
can improve the performance of your code.
We are going to use a very simple scenario
coming from the dynamic segmentation.
It is so simple that the benefit in terms of performance
will be really, really minimal.
With that said, it is important
to see the process of optimizing the code,
because we will use DAX Studio
in order to understand the query plan,
understand what the engine does,
and find the option of optimizing it by using variables.
It's just a tiny example
of much more complex and interesting patterns
that we show in the optimizing training.
With that said, let's go straight to the scenario.
In this model, we have a segmentation table.
You see, we have a segment low, medium, high
with the boundaries, minimum and maximum value.
The number I want to compute here in customer sales
is the sales amount of all the customers
that fall into the given segment.
So 913 are the sales of customers
that are in the low segment.
This pattern is very simple to solve
because it requires iterating over segments,
then over customers, and then check if the sales amount
of the given customer, because of context transition
is inside the two boundaries,
then we compute the sales amount.
Otherwise we just do not compute anything.
It is simple and it just works fine.
You can see that we have the results here.
But we are searching for optimization.
Where can we optimize this code?
You can note that we are using sales amount three times.
Here, here, and again, here.
We are computing the value of sales amount multiple times.
Is the engine smart enough to reduce
the number of calculations or not?
We are going to discover that.
And to do that, we need DAX Studio.
So we go to external tools and launch DAX Studio.
Okay, we need a query.
So let's start writing EVALUATE, SUMMARIZECOLUMNS.
And we group by segments segment.
And we compute amount, which is our customer sales.
Let's run the query, there are no errors.
And let me also add the definition of customer sales.
So we see the entire piece of code
in one single place, customer sales is here.
Define the measure.
And then we leave some space.
Let's get rid of that.
Now, what we want to do is understand what happened.
So what the engine computes.
To do that we enable the server timings
and we run the query once more.
The result is that we have now
the list of all the storage engine queries
executed by the query.
And we have several small queries.
This one and these ones,
which are really tiny
and are not worth investigating further.
But you see that we have three queries
that return a large number of rows.
The first one, let me expand the code, retrieves
the customer key and the row number from the customer.
So that is retrieving all the individual customers.
Then we have another query that computes sales amount.
You see that we have here, quantity times net price
from sales LEFT OUTER JOIN customer.
So this is a first scan of the sales table.
And then we have just below a second scan again
of the sales table
that seems to compute the very same number.
Indeed, there is a condition here,
which is not present in the previous one.
If you look at here, there are no conditions.
Here, there is a further condition for the customer key
to be inside a given range.
Now, the thing is, why do we have two queries?
Because in the code,
we are referencing three times the sales amount.
So I would have expected either three calculations
or one calculation.
One calculation if the engine is smart enough
to optimize the number of executions.
Three calculations, if it is not that smart.
The thing is, if you look at the code,
we are using sales amount twice inside the condition of AND.
So in this part of code, we are using sales amount twice.
And the engine optimize that.
It knows that it needs to compute the sales amount
for all the customers.
But then we compute sales amount
in another branch of the IF statement.
In this branch, the engine does not know
if it will need to compute sales amount
for all the customers or for a subset of customers.
If you look at the queries here,
the first one without any condition,
is the sales amount that feeds these rows.
The second one that contains the condition
is the sales amount that is used
in order to perform the sum.
And these IDs are the IDs of customers
that satisfy the condition of the IF statement.
In any case, we are computing sales amount multiple times.
And we want to discover whether using a variable
help us or not.
That's a perfect scenario
where using a variable proves to be useful.
Because we can create a variable sales amount
where we compute sales amount only once.
Let me do that.
And in the RETURN part, we use the variable
instead of using the measure.
And here.
Now we're using the variable three times.
And theoretically it should be computed only once.
Let's give that a try.
If I run the query now,
you see that we still have a query that retrieves
no, we do have the first query
that retrieves quantity times net price.
There are no conditions,
so this is the only calculation of sales amount.
This other query is retrieving the customer row number
and the customer key as it happened before.
So you have seen that by using a variable
you can reduce the number of calculation
of the same measure.
If we look at time, the benefit is really, really minimal.
You don't get anything in this specific scenario.
With that said, on a larger model
or with a more complex measure,
the benefit that you can obtain
from this simple optimization is quite good.
Beside, we can go one step further.
Because you see that in the code,
we have two iteration, one over segment,
and the other one over customer.
Having multiple iterations in a measure
is quite never a good idea.
How can we obtain a better result
by reducing the number of iteration,
the number of nested iteration?
We're iterating over customers, over segment,
and we would like to iterate over one table only.
Actually, we can do that.
Because, first we do not need to iterate over
the entire customer table.
Let me increase the font a bit.
We do not need to iterate over the entire customer table.
It would be enough to iterate over the values of customer,
customer key.
And same for segment, well, for segments, actually,
we need the minimum value and the maximum value.
So we actually need these two columns.
And beside, it's a table with only three rows.
But how can I reduce the number of nested iteration?
Well, I can transform that into a CROSSJOIN of segments
and VALUES of customer key.
Now, this CROSSJOIN returns one table only
that contains the two sets combined.
I can remove that and rewrite the code this way.
So first we use the variable, then we reduce the iteration.
Let's give this a try.
We run it once more.
And you see that the query plan is not faster,
it's 65 milliseconds.
Before, it was around 80 milliseconds,
but it's a bit simpler.
First, we have the calculation of the sales amount
for all the customers.
Then we retrieve the customer key
and the other two are just
the retrieval of the segment table.
So now we can look at the query plan
and it is indeed an optimal query plan.
As you have seen,
whenever you have multiple evaluations of the same measure,
it's a good thing using variables instead.
Because that reduces the effort of DAX
in order to compute the value multiple times.
You can find a lot of these small examples.
What I always say is that optimization starts
when you write the code.
What you need to do is learn these optimization techniques
and then you implement them
while writing the code the first time.
This is why it is so important
to understand the internals of the engine,
understand how DAX works,
because by using all these small techniques
your DAX code will be way better.
Enjoy DAX.