Slow performance with PowerBI Service and Desktop even though underlying query executes quickly

Community Support

5 years ago

Hi Anonymous ，

It seems to me that the expectation should be that any DirectQuery will be slower than a hosted model. You get an extra round-trip of latency from Power BI Service to SQL Server, along with potential bandwidth issues if the returned dataset is large (shouldn't be the case).

there are three things you can do:

create an index and see if that helps
bump up the performance characteristics of your SQL Database
explore in-memory option for your database

And you could 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

The following article I tink it is helpful for you,you could read it .

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

https://community.powerbi.com/t5/Desktop/Performance-issue-with-Direct-Query-with-SQL-Azure/td-p/14656

https://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables/td-p/23901

https://forum.enterprisedna.co/t/performance-comparison-between-directquery-dataimport/8052/3

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Best Regards

Lucien