Forum Discussion
On-Promise Gateway very Slow
Community ChampionDirect Query performance depends on a TON of factors, and will always perform worse than an Import Model.
You can have the best designed queries/views on the server, a great gateway machine, and a smoking hot database server, and it still perform bad. For example, Cumulative totals (YTD) or rolling totals always perform very poorly compared to their Import model counterparts, even with expert tuning on the server side. You can have a visual that is requesting data in a specific way and it is writing its own DAX query, which is being translated into a SQL query, that performs miserably.
The "fix" is convert to import unless DirectQuery is really necessary. Otherwise you will need to attack each specific visual's query and see how to optimize those.
See Import vs DirectQuery in Power BI - SQLBI for more, and look at Chris Webb's blog which often brings up issues that help make DQ queries run faster. Chris Webb's BI Blog: Power BI (crossjoin.co.uk)
But i would not make the assumption this is the gateway at all. I suspect it is not the bottleneck.
Hi,
Thank you for sharing that, very helpful.
1- I create a very simple query using a single table as visual, the performance is 230 ms ( Checked using the Performance Analyser)
2- Published that and still getting a very poor performancing executing it from the Web.
It is very fast running on my Computer, very slow from the Web.
Setup -
Import Mode
Cached data with the Gateway
- edhans
If it is fast on your PC and slow on the web, then it is the connection speed from the service to the gateway, or the gateway to the DB server, or both. You will need to work with your IT team to do some network analysis to see where the speed issues are and resolve those.
All good with the performance from Server to DBs.
Using Power BI Desktop it flyes.
Diogo Dalla
- edhans
No, I am talking about Microsoft's service to the gateway. Power BI Desktop ignores all of that as you are on the same network as the database.