Forum Discussion
Direct Query exceeds available resources
So if you were importing the data it would be fine. However in Direct Query everything is still being sent back to SQL server to process.
Have a look at the query plan that SQL Server uses when executing the join across the two sub queries (Display an Actual Execution Plan - SQL Server | Microsoft Docs). It might give you a clue.
There is some good advice in here: sql server - SQL Query performance that use subquery and joins - Stack Overflow
It's possible that adding some indexes to the base tables might help to assist the joins. Eg the individual queries are fast but in order to join them it's having to do full scans of the right hand table for every row of the left(certainly sounds like it).
An indexed view lets you take each query and save it as a view but also add indexes to assist in joining those views which will make it more explicit as to what you want SQL Server to do.Sorry if that's vague. Without seeing your setup it's a hard one to diagnose.
Hello bcdobbs ,
Thanks so much for your reply! You are spot on that the tables that make up my report are generated by SQL queries, consisting of several joins of tables in the native database.
I've read that particular article before I posted this here, but it looks like I didn't fully understand what they mean by "materializing" data transformations. In my view I was doing that by transforming the data in my native SQL queries, ensuring that the data was all ready when loaded to Power BI. Is that the way to go or is that not what they mean by that statement?
So if you were importing the data it would be fine. However in Direct Query everything is still being sent back to SQL server to process.
Have a look at the query plan that SQL Server uses when executing the join across the two sub queries (Display an Actual Execution Plan - SQL Server | Microsoft Docs). It might give you a clue.
There is some good advice in here: sql server - SQL Query performance that use subquery and joins - Stack Overflow
It's possible that adding some indexes to the base tables might help to assist the joins. Eg the individual queries are fast but in order to join them it's having to do full scans of the right hand table for every row of the left(certainly sounds like it).
An indexed view lets you take each query and save it as a view but also add indexes to assist in joining those views which will make it more explicit as to what you want SQL Server to do.
Sorry if that's vague. Without seeing your setup it's a hard one to diagnose.