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 ,
For information about using DirectQuery with SQL Server Analysis Services, see DirectQuery in SQL Server 2016 Analysis Services.
There are many facts can affect Power BI report render performance. I would suggest you go through this article and try to optimize reports then test again: https://docs.microsoft.com/power-bi/power-bi-reports-performance
Best Regards,
Winniz
Hi v-kkf-msft,
Thanks for your reply and documentation. We are now choosing for import mode storage as for the most cases 8 times refresh per day is enough. This gives us a substantion performance increase compared to DirectQuery. I'll have a look at the optimization techniques.