This month’s T-SQL Tuesday invitation from Jeff Taylor is probably the easiest one for me ever, mostly because my answer is the same as Jeff said:

Reaching for a temp table should be the exception, not the reflex.

Temp tables fit into my query writing process as one of those last ditch efforts to make a query execute fast enough. Of course it would have been harder if I had followed all the rules and added a lot of test cases, but I had a pretty busy week last week and am finishing this pretty late on Tuesday night, so I just gave my opinions.

When I am writing any query, I typically start with, well, requirements. Always start with a knowledge of what you are trying to pull out of the data. But then, once I have the requirements, I follow the following basic pattern.

• If at all possible, I write the query in one statement with no CTEs, derived tables, subqueries, etc. Just simple clauses. SELECT, FROM, WHERE, GROUP BY, HAVING and joins.
• If that isn’t possible, see if using a subquery makes sense.
• Then use a CTE or derived table to reshape some of the data to fit the need.
• Finally, if there is just something with the shape of the data, I break down and consider temp tables to take control over the execution order of the query.

Note that table variables are something I would rarely use other than for some row storage that isn’t transactional (like error messages) or parameters.

I have seen many queries where people start out trying to break a query into steps like in a procedural language and end up with a bunch of temp tables to somehow improve a query over just a simple, single statement query. There are a few reasons that kind of drive me nuts, because it always feels complexly complicated and rarely are their reasoned out comments explaining what people were thinking.

There is one ugly problem though, and that is that there are no query methods that are never useful in and of themselves. As the old saying goes “it depends”. Some queries give optimizers fits, especially when they are super complex and need to be broken up a bit. The problem is that they become default patterns people use for every query they write and it is insanely hard to read even simple queries.

The filter early myth – Using a temp table you can reduce the number of rows in a table that you are going to join or process. This can be a good plan…when the other methods fail. Sometimes you do have a huge table that can’t be reasonably filtered to a small set in your query and you can’t create indexes to make the reducing of a set work in a typical query plan.

But too often this is people falling into the pattern of thinking that:

WITH CTE AS (
    SELECT Column
    FROM    Table
    WHERE  Column > 100
)
SELECT Column, COUNT(*)
FROM   CTE
GROUP BY Column;

Is faster than:

SELECT Column, COUNT(*)
FROM     CTE
WHERE  Column > 100
GROUP BY Column;

Because you “filtered early in the processing”. The CTE in this example is generally benign and won’t usually hurt your performance because SQL Server is smart enough to use the same plan. But if that CTE seems to do some good for the query, then maybe this pattern will be great:

--I use CREATE TABLE...INSERT in this example, and
--SELECT INTO in the next. SELECT INTO can cause some metadata
--blocking in some situations. but if you aren't taking the time to understand
--how a query executes, you probably are  more concerned with having to 
--get a table declaration right and SELECT INTO will win every time.
CREATE TABLE #CTEKIller
(
     Column int 
)
INSERT INTO #CTEKIller (Column)
SELECT Column
FROM    Table
WHERE  Column > 100;
SELECT Column, COUNT(*)
FROM     #CTEKIller 
GROUP BY Column;

The problem here is that any index you would likely be using for the INSERT query will no longer be available for the second query.

I am using the table in multiple different ways, so I made copies – Like any of these examples, it is sometimes true. If you have a huge table and you just need a small number of rows that you can’t get in an efficient way, paying the price once in a temp table is not a bad idea.

But in most cases, if you are forced to use a CTE by your syntax, the optimizer can still optimize the query to be faster than you creating this new physical table that has no indexes or stats, that you may then need to create an index to use (or let SQL Server create a hash index so it can do a hash join).

Basically where you could have written:

SELECT Columns
FROM   Table1
         LEFT OUTER JOIN Table2 as T2_1
            ON Table1.Table1Id = T2_1.Table1Id
         LEFT OUTER JOIN Table2 as T2_2
            ON Table1.Table1Id = T2_2.Table1Id
WHERE T2_1.Value > 100
     AND   T2_2.Value <= 100
     AND  Table1.Status = ‘Active’;

You change one or both of the Table2 reference to temp tables and put the filters in the temp table queries. In this example, the two Table2 references are mutually exclusive, so maybe this helps?

Maybe, but almost certainly, this method could in fact be the best. The optimizer can treat the two references as if they were two tables and maybe use an index on both. Or it isn’t inconceivable that it could just scan the Value column values and do both joins at one time. Worst case though, is that it does just about the same amount of work with less code than your version.

So jumping right to something like this version:

SELECT Table2Id, Table1id, OtherColumns
INTO  #T2_1
FROM  Table2
Where  Table2.value > 100;
SELECT Table2Id, Table1id, OtherColumns
INTO  #T2_2
FROM  Table2
Where  Table2.value < 100;
SELECT Columns
FROM   Table1
        LEFT OUTER JOIN #T2_1
            ON Table1.Table1Id = #T2_1.Table1Id
        LEFT OUTER JOIN #T2_2
            ON Table1.Table1Id = #T2_2.Table1Id
WHERE Table1.Status = ‘Active’;

Now you have forced the optimizers hand. You will filter the Table2 value and store them, no matter what the values of Table1 we need based on the status.

Of course, without giving you row counts, indexes, cardinalities, selectivity, and more details you never really know. And this is why the first step for me is to write the simplest, most straightforward query that I can, that reads the way a query is written and try it. Once I know the answer is right, then I can performance tune the query if need be.

I find the logic easier to read when it is broken up – I don’t think we need an example here as you can see in the previous subsections that this is not going to be universally true. In fact, most of the time it means you have lots of up and down in the code trying to figure out what this temp table does and where this column and that one came from.

Summary: every query technique you know has merit

But simpler queries usually execute better because the optimizer can choose the best way, not the way you think is best.

Temp tables are no different. Sometimes, when you end up doing aggregates of aggregates of aggregates and you need to process a lot of data, using a temp table is going to be your friend. You can put the results in a temp table and make a query that runs 2 hours finish in 2 minutes. But you have to realize that those are the exceptions, not the rule. When you are doing most queries, putting as much as you can into a query using just the basic clauses.

Thing is, too many people find a pattern they think will do everything and they just blindly follow it. And since temp tables are so easy to create (and users are allowed to create temp tables when they have access to a server.)