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 & 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 & 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_ASandLatin1_General_CI_ASSQL 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.
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 & encapsulate all the logic in the stored procedure.
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 & 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 & 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_ASandLatin1_General_CI_ASSQL 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.
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 & encapsulate all the logic in the stored procedure.