Direct Query unstable performance

[joost_romijn]

Hi guys,

I've been having really unstable performance when using Direct Query. I have searched and read a lot of documentation about Direct Query and I am still not sure what is causing this unstable performance.

I have optimized and simplified all my DAX expressions, Power Query logic and even tested the dashboard with only a few objects. I have upgraded the underlaying Azure SQL database source. Still, the performance is very dynamic within either one configuration.

After a lot of tracing and using the new Performance Analyzer in Power BI I have found the following.

Sometimes the performance of the dashboard is good. All the objects in the dashboard are loaded withinin 1,500 miliseconds according to the Performance Analyzer. Sometimes the performance of the dashboard is slow, taking up to 20,000 miliseconds to load all objects in the dashboard within the same configuration.

In SQL Profiler I see that when the performance is slow, Power BI is logging all sorts of Notifications. It looks like PBI makes a new connection for every Direct Query it fires to the database. When the performance is good, within the same session, this is not the case.

See attached images for clarification.

Is there anyone that knows what causes these new connections or what these messages mean?

Best regards,

Joost Romijn

[ugurdemir]

This problem still exists

Environment Windows 10. PowerBI Desktop Version: 2.91.701.0 64-bit (March 2021)

Problem realization;

* Open powerBI desktop. Open. pibx file which is connected MS-SQL via direct query

(MS-SQL server installed on my own laptop.

There is no other client to request anything off my database on my network.

Database is not being used to enter data or something else.

Laptop is not serving to another client for any purpose)

* Open performance analyzer. Start recording.

* Refresh visuals. Max duration(ms)=2005.

* Clear

* Refresh visuals. Max duration(ms)=2082.

* Clear

* Refresh visuals. Max duration(ms)=1896.

* Clear

* Refresh visuals. Max duration(ms)=13521.

* Clear

* Refresh visuals. Max duration(ms)=4091.

* Clear

* Refresh visuals. Max duration(ms)=2147.

It's really weird.

[v-lili6-msft]

hi, @joost_romijn 

Please refer to this post to check if it could help you:

https://community.powerbi.com/t5/Desktop/Direct-Query-unstable-performance/m-p/732748?lightbox-messa...

Best Regards,

Lin

[joost_romijn]

Hi @v-lili6-msft ,

Thank you for your response.

But I think you pasted the wrong link?

Best regards,

Joost

[v-lili6-msft]

hi, @joost_romijn 

I'm sorry for it, this link:

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

Best Regards,

Lin

[joost_romijn]

Hi @v-lili6-msft,

I don't think this is an answer to the question. They are talking about Power Query and not about Direct Query.

Besides, my fact tables in this particular situation are not even that large.

Best regards,

Joost