Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Direct Query exceeds available resources

I have created a report using Direct Query for 8 SQL queries, which are related to each other in a star schema. When I run the individual queries in SSMS they're all done within a second (returning n...
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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.