Direct query and slow performance

[afbraga66]

Hello,

I am currently trying to improve a data model's performance that uses direct query, due the need of having near real-time data. The .pbix file size is like 900KB, so you get a sense of the tables rows which is also small (around the 10k - 50k rows). The data is stored in an Azure SQL Database and tables have defined primary keys. The issue is that the visuals take like 10 seconds to render. I have a visual per page, and the one I am using as example has 8 columns only. The final idea is to use Power BI Embedded service (currently A1), but even there it takes this amount of time. I have read documentation but still it isn't very clear on what can I do in Power BI or even at database level in order to improve this. Can you help me understanding what might be the issue?

Thank you.

Best regards,

André Braga

[v-kkf-msft · Accepted solution]

Hi @afbraga66 ,

I think your data size is tiny, so it looks like your data source and/or network performance is insufficient.

In such a case you want to consider aggregations. Use a hybrid storage model, you can specify which visuals require a query to back-end data sources. Visuals that don't require a query are imported even if they're based on DirectQuery. This feature helps improve performance and reduce back-end load. Please see Manage storage mode in Power BI Desktop  for details.

And Refer to the following documents to see if they help.

DirectQuery model guidance in Power BI Desktop 

Optimization guide for Power BI 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.