Search code examples
sqlsql-serverquery-performance

Should all sub queries be replaced with temporary tables?

I have been working on a solution (in SQL Server) where all the sub queries with no exception have been rewritten with temp tables in order to boost performance.

To give an example, all the queries like this:

SELECT something 
FROM (SELECT * FROM T1 WHERE condition1) 
JOIN ...

have been rewritten to be like this:

SELECT * 
INTO #tempTable 
FROM T1 
WHERE condition1

SELECT something 
FROM #tempTable  
JOIN ...

It has also been suggested here that all sub queries should be avoided in favor of temp tables.

Based on the given facts, Should all sub queries be replaced with temp table? If not when should one be considered over the other?

Solution

  • That is ridiculous. A joke.

    Your "subquery" is fine as is. SQL Server just ignores it. You could rewrite it as:

    SELECT something
FROM T1 JOIN . . .
WHERE condition1

    SQL Server should optimize this correctly.

    In my experience with SQL Server, there have been very few cases where creating a temporary table is needed for optimizing a query. A bit more often, I use query hints to avoid nested loop joins.

    If a temporary table is needed, then there would almost always be indexes on the table. That is one of the key reasons for using a temporary table. (The two others are because the same query block is repeated through one query or multiple queries).