On-Promise Gateway very Slow

TeigeGao you may have a clue on this one.

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

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.