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,
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.