SQL Server Performance

Correlated Subquery MUCH slower than same query using temp tables: Not enough RAM?

Discussion in 'T-SQL Performance Tuning for Developers' started by Maxer, Aug 17, 2007.

  1. Maxer New Member

    I wrote a query using a correlated subquery and it takes 5.78 seconds PER RECORD to return results. The SAME query, only I select the "outer" query into a temp table then run what was the correlated subquery against the temp table, only takes 0.60 seconds per record! It got me thinking, the server it runs on is a remote linked server (I don't have admin rights to it) could the issue be that the correlated subquery forces the server to reload all the tables into memory for each pass?
    It loads the outer query's tables into memory, gets a record, then it has to run the inner query against that record, so it loads the inner query's tables into RAM... gets the result.... then has to repeat the whole process over again?Would too little RAM on the server cause that sort of behavior?
  2. FrankKalis Moderator

    Posting the query and some more information makes this less like a shot in the dark. [:)]
  3. Maxer New Member

    I know, I know... but I can't post the actual query due to business issues... and well... I REALLY don't want to rewrite the whole thing as an example query :)
    I think I'm asking if that sort of thing is possible from a general standpoint?

Share This Page