- Hi friends.
In this video I want to show you how to
analyze a slow query in a report.
In this example we have many visuals and with
Performance Analyzer I can capture the information
for the execution time of each visual.
This way I will capture the slowest one
so I start recording and I click on refresh visuals.
At this point an important thing to do is highlighting
what is the query that has the biggest execution time
because it's what is causing
the slowest visual in the report.
And in order to retrieve this I have to sort by
DAX query time and I have to sort by in a descending way.
Doing that, I know that this matrix is
what is the slowest DAX query in the entire report.
And of course there is also other time to complete
the rendering and other but now we want to ignore it because
our focus is we want to test the execution time of this
DAX query and we want to possibly to optimize the code.
So in order to do that I can copy the query here
and once I have copied the query in the clipboard
I can paste the query in DAX Studio.
I connect DAX Studio to Power BI Desktop
and now when I paste the code here,
this is the very same query executed by the visual
that is displaying the matrix in Power BI Desktop.
At this point you can click run
and you run this query in DAX Studio.
You can see that this query shows the result which is
the first thing you can see in this pane but
another important consideration is how much time
we wait for executing the query.
This important information is contained here in the output.
You can see here that this query
has been executed in 340 milliseconds.
First of all we have to execute the same query
multiple times in order to make sure that
we are not measuring some noise caused by some
other program running on my computer for example.
So in order do that we can click run multiple times.
One two and now I go back to output and I can see that
the duration of the query is between
275 and 340 milliseconds.
At this point I can also consider,
wait a minute, am I executing the query
in the worst possible condition?
In order to do that, we have to make sure
we don't have any data in the cache
and this can be obtained, and it is a best practice
when you analyze the query in DAX Studio,
to run the query checking this option
which is Clear Cache and then run.
So with a single click, each click will clear the cache
and immediately after execute the query again.
Let's try to do this another three times
and see what happens, so one, two, and three.
Once we complete this and we go back to the output,
we can see that this time,
the execution time is considerably slower.
We are at one second and a half,
1,500 milliseconds, 1,577
and 515.
So you see that executing the query in the worst possible
condition also stabilize better the execution time.
Which is important when we want to analyze the performance.
At this stage we know that this query is considerably slow
especially when it is executed with a cold cache.
So the next thing to do is gathering more information
about the query plan and the execution timings.
These two options, query plan and server timings
are what I want to use in order to capture this information.
And so I click on query plan and I click on
server timings as soon as this operation completes
and when you see here in the output,
query trace started after you clicked on the button,
you can start your next execution with run.
Now as you see I don't look too much at the result,
at what I get in the result, I'm more interested
into the execution time in the output.
But at this point you see that we have other two panes here,
query plan and server timings that contains
additional information about my execution.
So let's see the query plan.
The query plan is the actual query plan of the query.
We have a physical query plan here
and a logical query plan here.
As you can see, this information is relatively complex.
If I scroll down just in the physical query plan
which is probably the more interesting part of the entire
of the two query plans we have available.
It's very long and believe me,
I don't use this information
as the first way to optimize the query.
If I can I try to move to the other pane
server timings here and server timings has a number
of information that are related to the execution time
of the query with some number that is important to know.
So let me describe what we have here.
Total is the total execution time,
how much time we wait.
This is the same amount of time we have seen
in the output window but this time it's split
between formula engine and storage engine.
And the formula engine is the time spent waiting
for the formula engine and the SE is
the time spent waiting for the storage engine.
So the sum of these two numbers correspond to the total.
Now usually a DAX expression
should be evaluated as much as possible
in the storage engine and then in the formula engine
because in theory the storage engine is
faster than the formula engine but these details
are something that you need to understand
looking at how the VertiPaq engine works.
There are other videos and other courses for that.
Now let's just focus at the very high level
about what is the problem of this query.
Once I realize I have a problem between formula engine
and storage engine I can also look at the other information
like the number of storage engine queries,
which is the number of requests made by
the formula engine towards the storage engine.
And here you have the details about each single
storage engine request made by the formula engine.
So you see that we have already a complex situation,
a complex query plan and a lot of information
about the storage engine.
So let me spend some time looking
at the query that we are executing.
Now if you look at this query,
you can notice that this is some DAX code
that you might not have seen before
because this is the syntax of a query in DAX,
whereas you might be used to writing DAX measures.
And what is the DAX measure used here?
Actually I know that this is customers,
the customers measure that we have in the report,
but the easiest way to find this information
is going back to DAX Studio,
clicking on the matrix here.
This click highlights this visualization
and because I selected this visualization
I have here in values, customers,
which is the only measure I have.
What can I do at this point?
I can include this measure here in my query
by searching for the measure Sales Amount here.
You see that I can click here,
I can click on sales amount in this list
and I can right-click and click on Define Measure.
When I do this, the definition of the measure is included
in my DAX query and I can change, for example,
I can format the query so that it is more readable.
This is a good idea.
And once I have this I can take a look at the sales amount.
I should have used customers so let me do this again.
Customers is the measure I want to include.
I right-click here and I include
the definition of customers.
I click on Format Query again and now I can focus
my attention on this definition of the customers measure.
Why I want to have the definition of the measure here?
'Cause now if I want to test the behavior of the measure,
changing something I will just change the code here,
I click run again and I test the same query
with a different version of my measure customers.
And this time I can tell you
this measure is easy to optimize.
Why?
This query is violating one of the best practices in DAX.
Filter columns don't filter tables in CALCULATE.
And what are we doing here?
FILTER sales.
So I can tell you this is an error following the best
practices in DAX and the right thing to do is to
filter just the column Quantity greater than zero.
But in order to not change the semantics of this query,
I have to make sure because I was filtering a table before,
I have to include this condition in a KEEPFILTERS.
This way I'm sure that I will obtain the same result,
but in case you're not, pay attention before executing
the query, you should go in the results pane.
You should take a look at the results of the numbers
you see here and in the next execution
we have to make sure we have the same numbers.
Let me try.
I click run now,
remember I am clearing the cache and then run, and
I have an error here because I probably forgot something.
Yes because I removed the filter before and I still kept
the ending bracket of the FILTER so now I fixed the code,
I run this again and if I go to results,
you see that I have the same number we have seen before.
You can go back to view the video to check the numbers
are the same but believe me, they are the same.
Now if I go to the server timings I discover that
all of a sudden, my query has been executed
in 12 milliseconds instead of one second and a half.
And why this?
Because my best practice application
to the formula, reduced the time for the storage engine,
for the formula engine,
the number of queries and the overall execution time
is now down to 10 milliseconds.
So what should I do at this point?
I just have to select this measure here in DAX Studio,
copy and paste this measure as the new version of customers,
of the customers measures in Power BI.
Enjoy DAX!