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

Hey Anonymous 

 

personally I do not consider a query quick that takes 7sec to provide a result, except a huge amount of rows is returned.

 

Of course, you have to add additional time to transfer data from the data source (I'm surprised that Power BI Service is faster than Desktop) and also to render the data.

 

You can use the Performance Analyzer to trace what's going on:

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Microsoft Docs

 

And finally, if you are using transformations inside Power Query that do not fold or using DAX to create measures on top of a direct query, this can also slow down the final "presentation".

 

---

Anonymous wrote:

...It looks like a similar performance issues have been seen when using other query engines, but no real solution has been provided.  Has any encountered similar issues?

---

I'm interested in the above statement, as I'm working with a large Power BI environment, with many direct query datasets (some respond blazing fast, some don't, but none is slow), I want to avoid the bad experiences you have made. Maybe you can be so kind and provide more details.

 

Hopefully, this provides some ideas on how to tackle your challenge.

 

Regards,

Tom

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:

1. create an index and see if that helps
2. bump up the performance characteristics of your SQL Database
3. 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