Direct Query is extremely slow

[AlexaderMilland]

So I'm trying to build a direct query PowerBI report. My model is EXTREMELY simple with no complex measures yet, however the load time is attrocious. We are talking multiple minutes or "visual has exceeded" on measures that would take milliseconds to complete with imported data. 

My model has a product table (30k lines), a orderline table (7 mill lines), a order table (2 mill lines), a date table, and a time table. 

My measures are all just SUMX over the tables for like profits, sales, items sold etc. 

I'm pretty new to direct queries and I'm wondering where to troubleshoot performance.

- The SQL server it's gathering data from has very little extra RAM avaible.

- The SQL server has very few indexes.

- The SQL server is updating data every 5 minuttes (incremental loads from another SQL server)

- I don't think there is relationships on the SQL server, but only in my PBI model.

- There is quite a lot of visualations on the PBI page, 15 unique ones with 1-2 measures each. The SQL server is running 10 queries (some are identical) when i refresh / make a new filtering. 

Which of these would you think is a bigger culprit? 

Also would limiting the data inside an SQL view (like limititng the date period) improve performance? Or does it not matter since the view is also just querying on the big underlying table? The visuals would have a limitted date period on them anyway inside PBI

[Anonymous]

Hi @AlexaderMilland,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[cpwebb · Accepted solution]

Have you followed all the tips listed here? https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance Settings like "assume referential integrity" can make a big difference.

Also you can experiment with increasing (if you're on Premium) or decreasing the number of connections used: https://blog.crossjoin.co.uk/2022/06/12/how-the-maximum-connections-per-data-source-property-on-powe...

Once you've done all that you'll need to tune the SQL queries that Power BI generates on the database side - from the sound of it, the main problem is in SQL Server and not Power BI.

Chris