Forum Discussion
Performance issues with DirectQuery, possibly due to gateway?
Hi Anonymous ,
I think the first qyestion to ask, is why are you using Direct Query ? Do you have multiple million rows to report on, or require real-time reporting (or both) ? Because I think DirectQuery is part of your problem. As you are already finding out, connecting Reports to a DirectQuery Dataset will generate an SQL query for every Visual on the Report and every Slicer, this will then generate another set of SQL queries whenever the Page is changed, a slicer is clicked, or an element on a Visual is clicked (cross filtering). Unless your Database is configured to expect this extra load, and the Gateway is able to keep up with the traffic, you will experience pauses and slowdowns. Moving the Gateway to a dedicated server and the SQL database to another dedicated server may help, but unless you really really need real-time data reporting you are probably better to go with an Import model not Direct Query.
see...
Import vs DirectQuery in Power BI - SQLBI
Hope this helps
Stuart
Hi Anonymous ,
I think the first qyestion to ask, is why are you using Direct Query ? Do you have multiple million rows to report on, or require real-time reporting (or both) ? Because I think DirectQuery is part of your problem. As you are already finding out, connecting Reports to a DirectQuery Dataset will generate an SQL query for every Visual on the Report and every Slicer, this will then generate another set of SQL queries whenever the Page is changed, a slicer is clicked, or an element on a Visual is clicked (cross filtering). Unless your Database is configured to expect this extra load, and the Gateway is able to keep up with the traffic, you will experience pauses and slowdowns. Moving the Gateway to a dedicated server and the SQL database to another dedicated server may help, but unless you really really need real-time data reporting you are probably better to go with an Import model not Direct Query.
see...
Import vs DirectQuery in Power BI - SQLBI
Hope this helps
Stuart
Hi Burningsuit, Stuart,
Thanks for your reply. The reason we went with DirectQuery is we're coming from a live connection to an SSAS cube, and users are used to having realtime reports. Sometimes information is added to a datasource and needed in reports within the hour, so with current processes, near real time reporting is desired. The live connections seems to perform a lot better than direct query, even with the SSAS cube on the same machine. I must admit, I'm not familiar yet with performance aspects of live connection vs. DirectQuery and assumed they would perform about the same.
On the lowest granularity, and unfortunately some users want to analyze on this level, to a few years back, we do have multiple million records. We've modelled our DWH so that regular SQL queries written by us perform well. Unfortunately, we can't influence how PowerBI queries the model, and now see that these queries do not run fast on our views.
Seems a possible solution might be to offer imported datasets for reports where realtime data is not neccesary and stick with directquery for near real time data. We might investigate what a dedicated machine could do in terms of performance.
Hi Burningsuit,
Thanks to your input we explored the import model and will now choose this as our main solution. The performance is a lot better than DirectQuery, and the data and refresh options are good in 99% of our cases.
Thanks for your input.