3

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?

Community
  • 1
  • 1
Saber
  • 5,150
  • 4
  • 31
  • 43
  • This already answered in here:
    http://stackoverflow.com/questions/16767645/why-is-there-a-huge-performance-difference-between-temp-table-and-subselect and this:
    http://stackoverflow.com/questions/2825342/why-would-using-a-temp-table-be-faster-than-a-nested-query     – Putra Christianto Purba Mar 13 '17 at 20:08
  • 4
    The only absolute with no exception rule in sql server is "it depends". I would argue quite strongly that just blindly making every subquery into a temp table is an exercise in futility and frustration. In some cases it may increase performance but in others it will just as likely make it worse. Consider a subquery with a million rows. While this may not be a great way to write code copying that much data to a temp table is going to be slower. And at best you may be "fixing" a performance problem that doesn't even exist. – Sean Lange Mar 13 '17 at 20:08
  • 1
    This is pretty much the answer you are looking for: http://stackoverflow.com/a/11169910/2333499 – SqlZim Mar 13 '17 at 20:15

2 Answers2

8

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • There's also a nice link that @SqlZim gave - "Temporary tables are a different matter, because you are providing more guidance on how the query should be run. One major difference is that the optimizer can use statistics from the temporary table to establish its query plan. This can result in performance gains". The answer was given by ... mmm ... – David דודו Markovitz Mar 13 '17 at 20:22
  • 1
    @scsimon - Therefore the "The answer was given by ... mmm ..." :-) – David דודו Markovitz Mar 13 '17 at 20:59
  • It was the second link in the second answer from the link that OP posted in their question. He probably deserves an upvote for citing a good source there: http://stackoverflow.com/a/16768252/2333499 – SqlZim Mar 13 '17 at 21:29
0

One point I don't see often in the discussion about subqueries is the human factor, specifically the readability of nested objects and the maintainability of the code.

Much like other forms of human-read code, it is important that people running and editing queries can understand what they are running and how different parts of the query are interacting with each other. Beyond simple subqueries retrieving and passing only a few elements, increasingly complex subqueries can become more difficult to read and understand the query (and how it relates to its superquery), as well as potentially harder to maintain for other reasons than time spent understanding the query (such as if there is a lot of code duplication involved in the nesting).

What's more, what starts as a simple subquery may expand to become more complex, at which point you may have to extract the subquery to a temp table in order to make it readable / deal with genuine performance issues (of the sort that Gordon Linoff mentioned). Depending on how comfortable team members are with such refactoring and any dependancies on scripts using subqueries (such as with stored procedures), such a possibility of having to add time for refactoring might mean that a manager would introduce temp tables as a style preference as much as a "performance boost".

Myles
  • 543
  • 8
  • 13