I recently developed a stored procedure which essentially queries data from several different tables, manipulates it, and then inserts the result in a certain table.

It ended up being one INSERT statement with many subqueries and was around 300 lines. The code was very performant; however, it is not very readable and may have other drawbacks.

Is it within recommended usage to write a query like so:

INTO #tempfoo
FROM foo;

INTO #tempbar
FROM bar;

FROM #tempfoo
     INNER JOIN #tempbar ON #tempfoo.id = #tempbar.id;

Or is it better to write it like this, even though it can become somewhat unreadable:

    SELECT ...
    FROM foo
) AS foo
    SELECT ...
    FROM bar
) AS bar ON foo.id = bar.id;

Note: I know this is a simple example, but imagine if you have layers of nested subqueries and four or more table joins.

  • 4
    Have you tried using CTE instead? I mean it seems a bit subjective as to whether it's readable or not. I find CTE easier to read but others dont. As long as you write everything out cleanly and comment well, you should be okay IMO.
    – SQLDevDBA
    May 1 '18 at 13:35
  • 3
    I have found that with SQL especially, performant and readability of style can often be at odds with each other :) +1 for trying a CTE May 1 '18 at 13:38
  • 1
    What does the query plan look like for both and what is the table structure? Things I think about looking at that: How many reads and writes are you performing? Is it using the proper indexes? If you do those tempdb inserts, is it going to fill the T-LOG or expand the tempdb drive? Are the joins on primary keys / clustered indexes, non clustered indexes, or is there repeating data elements in each table that need to be filtered before it gets joined? Are you going to index the temp tables after the inserts? I look at standard practices -> performance -> scalability -> readability when coding. May 1 '18 at 13:50
  • 6
    There isn't a perfect answer to this question. Not only because readability is subjective but also because depending on the data, indexes, and how complex this query really gets, there will be cases where using #temp tables will be better, and cases where it will be worse. May 1 '18 at 14:01
  • Thanks; I found that CTE's were a great choice to make the code more modular/readable
    – N4v
    May 2 '18 at 17:01

There are times when the indexes on underlying tables just aren't enough to satisfy a particularly big query with lots of joins/applies/subqueries/CTEs. For those times, temp tables and table variables can be just what you need to improve performance.

By using a temp table to store intermediate results, you can create any indexes or statistics that you need to improve further processing. I run into this now and then when we need to build an occasional-use report on top of a bunch of OLTP data. Table variables can be used to similar ends, but with the restriction that they're not nearly as flexible with indexing (up until 2014, at least, which began to remove much of this restriction). But they are a bit lighter-weight, and more importantly they are scoped to the module rather than session, so you don't have to worry about naming conflicts like you would with temp tables.

There is overhead associated with creating and writing to temp tables, of course, to say nothing of the impact on code maintainability, so if the query can be written cleanly as a single query making use of set-based operations without sacrificing performance, then stick with that approach.

  • Just yesterday I had to refactor a query with eight CTEs, into two queries (adding an indexed temp table in the middle).
    – RonJohn
    Jun 7 '20 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.