Direct Query is extremely slow

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-power-bi-directquery-datasets-can-affect-report-performance/

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