- Ciao friends.
In this video, I want to perform
a quick comparison of the performances of
DirectQuery versus Import Mode in Power BI.
A lot of people made the wrong choice
by choosing DirectQuery
when they could live much more happily with Power BI.
Let's start by looking at
where you made the choice for the first time.
Now when you create a model,
an empty model like this one,
you need to start loading some data.
For example, I can load from SQL Server.
I need to provide the Server name, the Database,
and then I need to choose Data Connectivity mode
between Import and DirectQuery.
This choice is extremely important.
Because if you choose Import mode,
you will load data from the server
into the internal database.
If you choose DirectQuery,
you just connect to the SQL Server database,
and every time you run a query,
you will need to wait for the SQL Server to return data.
You have real time data, but it will be a bit slower.
How much slower?
In order to understand it, I created a model.
The model in already in DirectQuery mode.
Let's take a look at it.
I have this model which is the Contoso model
that I created using DirectQuery.
So these tables are all loaded in DirectQuery.
We have sales, customer, product, and so on.
Performance are just okay.
You see that if select one year, for example,
it takes a while before it returns some number.
We have no clear idea about the timing,
but we have the feeling that something is wrong.
So, where do we start understanding where the problem is?
Well, we can enable the Performance analyzer.
So to grab the query, start recording, refresh the visual.
Because what we want to do is
retrieve the query which is being executed,
and then optimize it in some way.
We see from here it took six seconds.
Let me copy the query
and then from External Tools we launch DAX Studio,
so we will be able to check and monitor
the performance of the query.
If I copy the query,
you see that we have a lot of SQL code.
At the beginning we have the DAX code,
and after that there is the SQL Query executed.
I'm not interested in SQL so, I just remove everything.
Enable Server Timings and run the query for the first time.
Okay, Server Timings is enabled, let's run the query.
So we have a first baseline.
I don't want to see the result,
instead I want to focus on how long it takes,
and now we have a precise measurement.
It says 6.8 seconds is the time needed to run this query.
What kind of performance can we expect
if we change the mode from DirectQuery to Import mode?
Let's try that out.
I can simply reduce that,
and change the storage mode of my tables.
To do that I need to go to the diagram view,
select the table that I want to change.
Right now I selected everything,
and in the Advanced tab,
I can choose between DirectQuery and Import mode.
If I choose from DirectQuery to Import,
the engine warns me that this operation
cannot be taken back.
I'm confident that it will work,
so I just make it run and right now
the engine is loading the entire data set
in memory and creating the VertiPaq Engine.
Now the same model is in VertiPaq,
instead of being DirectQuery.
You can already notice that it is much faster
by simply browsing numbers around,
but we can actually measure it.
We still have the query, around seven seconds
is the time it was taking earlier, and if we run it,
it's going to take six milliseconds.
That is a thousand times faster.
Now you cannot expect to always have queries
which are a thousand times faster.
The reason is that I loaded data from Contoso
through some views,
and the Contoso data model is not optimized
to run in DirectQuery.
If you work with very large models,
it is likely that your models are optimized
to run on DirectQuery.
So, the SQL Server is designed
in such a way that DirectQuery query will be super fast.
So what kind of performance difference can you
expect from a model which is
professionally built, optimized for DirectQuery?
It happens that I have one.
I have a model with 4 billion rows,
and I have both in DirectQuery and in VertiPaq mode.
We will run a simple query
in order to understand the performance impact
on an optimized model.
So, let's go again to the demo.
I already have the model ready.
I need to go here
launch DAX Studio.
Oh, actually I already have it here.
And let's start connecting with DirectQuery mode.
This is connected with DirectQuery.
And we can run the query.
You see that we have 4 billion rows in the model.
In order to save some time
I don't want to spend time here looking at you,
or the query spinning.
I already executed the query,
and it takes this query that grabs by period,
the average age of my customers
executing around 26 seconds in DirectQuery.
Of course it need to scan 4 billion rows,
so 26 seconds looks okay.
What are the performance if I connect,
if I ran the same query to the model in DirectQuery?
Instead of using the DirectQuery mode,
I go for the VertiPaq one.
Connect, enable again Server Timings.
We need to wait for the trace to reconfigure.
And then we run the query.
It will not be a thousand times faster,
because now I am no longer cheating.
I'm really doing a real-world comparison.
It runs in four seconds.
4 compared to 26, which is 5, 6 times faster.
This kind of performance is what you can expect
from a model that is already built for DirectQuery.
DirectQuery is quite never faster than VertiPaq.
VertiPaq is extremely fast,
and there is no way DirectQuery will be able to beat it.
Nevertheless, keep in mind,
four or five times on a model optimized for DirectQuery.
If you are in a more realistic situation,
where your SQL Server is not optimized for DirectQuery,
then ten, a hundred times faster
is the kind of performance improvement that you can expect
from using Import mode.
Whenever you have the option of
going in Import mode against DirectQuery,
go for Import mode.
DirectQuery is a professional tool
designed only for people that create very large database,
or they have the need of real time.
If you don't have such a need, go for Import mode.
Enjoy DAX!
(upbeat music)