Optimize a Sql subquery containing multiple inner joins and aggregate functions
I see that in your question you said:


  "I am unable to move the subquery out to a temporary table as temporary tables are simply not supported within the programming language used by my application."


But, have you considered calling a stored procedure instead? Is this even an option, considering the limitations with the programming language?

If this is a viable option, you could simply have the results of your subquery inserted into a temp table transparently &amp; encapsulate all the logic in the stored procedure.



Edit

I got to thinking about this some more, and perhaps the columns that you're using in your JOIN condition are of different collations. While this will usually result in a specific error message, there may be some implicit collation coversion occurring instead (see: MSDN: Collation Precedence (Transact-SQL)) between the sub-query &amp; the data being joined.

Here are a few links about collation that might be useful to you:


Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS
Collation Hell (Part 1)
SQL SERVER – Find Collation of Database and Table Column Using T-SQL
SQL SERVER – Change Collation of Database Column – T-SQL Script


Also, you may be able to trick your programming language into using a temp table with syntax like this:

SELECT *
  FROM tempdb..#MyTempTable


Just keep in mind that sometimes the temp database has a different collation then the data you're working with too, in which case you'll need to explicitly convert the data to/from each collation.