On-Promise Gateway very Slow

[Diogo_Dalla]

Hello,

I have a On-Promisse Gateway installed in an dedicated server ( 16 Gbs RAM) located in a region close to where I am, using with a single report with a tunned query perfoming very well, which is very slow.

My report is using Direct Query.

Thank you for any contribution.

Diogo Dalla

[Adamboer]

If your report is using Direct Query, it could be slower than if it were using Import mode because the queries are being executed directly against the data source in real time. However, there are several things you can do to improve the performance of your Direct Query report:

1. Optimize your query: Make sure your query is as efficient as possible by limiting the amount of data returned, avoiding unnecessary joins or subqueries, and using appropriate indexing.

2. Use query folding: Query folding is a feature in Power BI that automatically converts your query to a more optimized form before sending it to the data source. To take advantage of query folding, make sure your query meets the criteria for folding, such as using simple filters and not using custom functions.

3. Increase the capacity of your server: If your report is still slow even after optimizing your query and using query folding, you may need to increase the capacity of your server. This could involve adding more RAM or upgrading the CPU.

4. Consider using Import mode: If your Direct Query report is still too slow, you may want to consider using Import mode instead. Import mode allows you to import data into Power BI and store it in memory, which can significantly improve performance.

5. Monitor the performance of your report: Use the Performance Analyzer feature in Power BI to identify bottlenecks and slow-running queries in your report. This can help you pinpoint areas that need improvement and optimize your report for better performance.

By following these tips, you should be able to improve the performance of your Direct Query report and make it run faster.

[Diogo_Dalla]

Hello, 

thank you very much for your inputs.

My query is in Import mode now, I am using a dedicated server AWS T3.Large 16 Gbs RAM( As requested by MSoft).

Slownees appear to be between Power BI Service and End user.

Best regards,

Diogo Dalla

[edhans]

If it is still slow for end users after converting to import, how large is the model? Is it in a Pro workspace? And again, could be a service->your network bottleneck. IT network specialists would be able to assist with that troubleshooting.

[edhans]

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

[Diogo_Dalla]

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. 

[Diogo_Dalla]

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.

[Diogo_Dalla]

@ibarrau @edhans 

[Diogo_Dalla]

@TeigeGao you may have a clue on this one.