You Probably Shouldn’t Index Your Temp Tables.

Indexing
35 Comments

When you’ve got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get done more quickly. However, in most cases – not all, but most – that’s a bad idea.

To explain why, I’m going to take a large Stack Overflow database and write a stored procedure:

The first statement loads about 10 million rows into a temp table, and the second statement only pulls out the rows that match who we’re looking for. Now, that probably seems dumb: you’re over there yelling, “Brent, why would you put millions of rows into a temp table that you don’t need?” The answer is that I need to write a blog post quickly in order to explain a concept that I’ve had to teach clients a few times. I can’t copy/paste their real code here. That would be Bad™. Instead, let’s just keep going with this game for a minute.

I’ll turn on time statistics so I can get a rough idea of where SQL Server is spending its time – the first statement or the second:

I don’t often use time statistics here on the blog, so a quick explanation: in the Messages tab in SSMS, you get a line for each statement in the batch, plus a total:

The first statement – loading the temp table – took about 4.5 seconds.

The second statement – finding Brent – took about a second.

Could an index on DisplayName speed up the second query?

To find out, let’s add a new version of our stored procedure – this time one that creates an index on DisplayName before our data is loaded:

Now, when we run the new query:

The time statistics paint a horrifying picture:

Sure, the second statement drops from 1,017 milliseconds to 75, but…who cares?!?! The additional overhead of building the index is much, much higher, making the query take ten times longer overall.

Part of the problem is that our heap (table) has to be loaded first, and then the data has to be sorted by DisplayName, and then an index has to be created on DisplayName. We can’t do them both in parallel at the same time because the nonclustered index has to be able to point back to a specific row in the heap – and to do that, we need its physical location, like I talk about in How to Think Like the Engine.

Fine. What about a clustered index?

We can reduce the overhead of the process by only having one structure to store the data. Instead of a heap plus a nonclustered index on DisplayName, we can just define a single structure for the temp table: a clustered index on DisplayName. It can’t be a unique index because multiple users share the same DisplayName, but that’s okay. Here we go:

And when we execute it:

At first, it looks way faster than the last method:

Execution time dropped from 57 seconds down to 18 seconds, but there’s a catch. A big part of the reason why it’s faster is that now the query is going parallel. Note that CPU time is higher than elapsed time – that’s your clue that the query went parallel across more CPU cores. Now we’re going to have a CPU problem if a bunch of these queries run simultaneously.

And it’s still not as fast as our original solution, the heap.

So how do we make temp tables faster?

Only load them with the data you actually need. When loading temp tables – or any objects, really – be ruthless about filtering as early as possible.

If you’re only going to access the data once, leave it as a heap. Indexes make the most sense when the temp table is going to be reused repeatedly across lots of statements that all do filtering or joining or sorting using the same keys.

Try CTEs instead. If you have a multi-step process that involves a lot of filtering and joining, let SQL Server recalculate where the filtering logic should happen. I am by no means saying that CTEs are always better than temp tables – often it’s the reverse – but if you’re hitting a performance tuning wall on queries that use temp tables, try converting them to CTEs. SQL Server will reorder operations – it doesn’t have to execute the first CTE first, then the second CTE second, and so forth.

 

Previous Post
Mark Your Calendars: Free Live SQL Server Training Classes in October & November.
Next Post
Most T-SQL Queries Don’t Even Try to Handle Errors.

35 Comments. Leave new

  • As is often the case with your advice, you deftly bait me into wanting to argue specific exceptions (never shrink a db! index frag doesn’t matter! and other greatest hits) when I actually agree with the principal, haha. I guess it depends how much control you have on the codebase…if your temp table is literally just going to be scanned and joined to another table, then yeah, indexes are useless overhead. Although sometimes we might have a large many-step proc with loads of temp tables…suboptimal design usually, but occasionally improved with careful indexing. Lipstick on a pig, maybe, but sometimes the index ends up improving things (when a major refactor isn’t practical).

    I’ve also seen instances where we’ve had to break apart a monstrosity of a single statement (hundreds/thousands of lines with CTEs and subqueries) into more atomic steps with temp tables because the Optimizer just didn’t do great picking a “good enough” plan given the complexity. So there’s a balance to be struck with either artificially breaking apart a query into steps (not trusting the Optimizer and forcing the logic into separate stages) and collapsing it into one logical statement and trusting the Optimizer to do its thing. I’ve had good results moving things in either direction…depending on the query and the problems involved. But again, here we are in violent agreement as you acknowledge either might be superior, depending on the situation…and I agree with the principal of trying to let the Optimizer have the most freedom to do its thing (see also query hints) as a default, before trying to force its hand…it is usually better at it than us (until eventually it isn’t). As an aside, I wonder if some of the sequential temptable1-temptable2-temptable3 type coding is more a result of the developer trying to wrap their head around the problem logically, and that’s just how they work through it…even if it is suboptimal in performance (which likely they aren’t even thinking about at that point).

    Reply
    • We have definitely had to refactor some larger T-SQL with multiple CTEs for performance. The rule that we follow is that if you are going to reference a CTE more than twice, or if you ever join a CTE to itself later down the chain, it’s time for a temp table!

      Reply
    • Nic – I wonder if there’s anything in the title of the post itself that suggests that there might be exceptions.

      I mean, I know a lot of people won’t read the entire body of the blog post – but I wonder if we’ll someday get to the point where people will read the entire title?

      I guess we’ll never know. 😉

      Reply
      • Brent, you don’t understand.

        Someone is wrong on the internet.

        https://xkcd.com/386/

        Reply
      • Honestly I was making fun of my own fault there, not yours…as may not have been obvious, I suppose. Just that you have a funny gift for making things sound sweeping (like the examples I mentioned) which ignites the pedantic types (cough cough) into argument mode, when you actually did allow for exceptions…and I ended up realizing, yeah I agree. I did read your post…I was actually referring to your last paragraph where you say “often it’s the reverse” in my last paragraph. Ah well, tone can be hard to convey on the internet…I meant no criticism, was just commenting on the way it made me (incorrectly) start to think argumentatively…I will say, that isn’t a bad social media gift! (steak-umm bless) 😉

        Reply
        • AH! Hahaha, got it.

          I do love using the grab-’em-by-the-shoulders-and-shake-’em headline style because it makes people rethink their assumptions. If I titled it, “The Pros and Cons of Indexing Temp Tables,” it would sail through the night without anyone reading it.

          Like, uh…the product documentation, sadly. (sigh)

          Reply
          • Exactly…its like a benign sort of “motte and bailey”, the bailey being a slightly more controversial sounding title, the motte being its uncontroversial and nuanced contents. If you write “Never Ever Use DBCC Shrinkfile Again So Help Me God I Will Find You” or “DBAs Who Still Use Profiler Should Shampoo My …” [err nevermind, Jack Nicholson quote], it drives engagement with a bunch of us argumentative types that might have glossed over articles titled “Excessive Use of Shrinkfile is Costing You Fragmentation and IO” or “Time to Get On Board the Extended Events Train You Guys!”….even if the article itself is pretty nuanced and balanced. Anyway, it got me here…which made me laugh at myself, as I fell for the same thing…I’m just saying you’re good at it 🙂 Cheers…

  • Alex Zhuravel
    August 19, 2021 3:41 pm

    Thanks Brent,
    brief and to the point.

    Reply
  • Typically I only ever index temp tables if I am going to query those columns more than once. And when I do create an index, I do it AFTER the table is already loaded. It is much more efficient that way.

    Reply
  • I’ve found the same thing. I tend to want to index my (rare) temp tables, but then once I’m in testing with significant data, I find that the index costs more than it benefits.

    Reply
  • I would index the temp table AFTER it is loaded.

    Reply
    • Great. Do the above demo with that technique and see how it goes.

      (Spoiler alert: it isn’t faster than the heap approach.)

      Reply
      • Chuckle. I’ll take your word for it. However, it is faster than indexing before you load.

        Reply
        • Wow, twice in a row you’ve made a statement as fact without testing it and showing documentation, despite someone building the entire demo for you and giving you a free database to test it with. Interesting strategy.

          Tell me, why do you bother reading blogs when you’ve clearly got all of the correct answers?

          Reply
  • I agree! Why index a temporary table when you should filter before it gets there? Surely the source has indexes already created that would speed up fetching applicable rows.

    In regards to CTE’s: What is your view on joining CTEs within CTEs? I don’t know how to exactly describe this, almost like nesting them in a fall-through manner, where your first CTE is used as a join in the second to filter and onward, perhaps even including row_number() to select first instances, etc…

    Reply
    • CTEs are pretty far outside the scope of this blog post, but I do talk about ’em in my Mastering Query Tuning class. (Just got done with that module yesterday afternoon so it’s fresh in my mind.)

      Reply
      • Ah, I asked because you mentioned using CTEs in place of indexed temporary tables. I have watched nearly all of your videos and just got signed up for the free classes that were appended to this blog notification email. Very excited for those! They aren’t the mastering classes, but I enjoy the fundamentals and look forward to the refresher. Perhaps next years training budget can be used to get my hands on some of those mastering classes.

        Reply
  • Just for grins and giggles, I ran a quick audit of a couple of a custom code dbs (where in house developers put their stored procs):

    select object_name(object_id),
    SUBSTRING(definition,PATINDEX(‘% INDEX %’,UPPER(definition))-20,100)
    from sys.sql_modules where UPPER(definition) like ‘% INDEX %’

    Spotchecking it, looks like close to 300 procs have at least one temp table index creation! I ordered it by LEN(definition) ascending to pick a small, simple one, and I’ll play around with it, see where the index falls on the futility-usefulness scale. Fun exercise, thanks Brent!

    Reply
    • In my sample proc…the time difference wasn’t really noticeable and the logical reads diff was slight (112k vs 113k…index creation added only 1k reads), but the real painful thing: TABLE SCAN in the plan. Optimizer threw that index straight into the trash and went for the heap instead. Oh well, its the thought that counts right? LOL

      Reply
  • I don’t mind being proven wrong and I would say so if I were; however, I don’t have that database on my machine(s) to test it. You, on the other hand could move your create index after the insert fairly quickly and run the test. I’m the first to admit that what I’ve learned over 25 years sometimes seems totally inadequate which is why I read your blog. Most of what you say confirms what I’ve learned through trial and error dating back to SQL Server 6.5 and usually I don’t have any argument with anything you say. I wasn’t really arguing with you here and I meant no disrespect. I just know that I have used the strategy that I described, successfully, with order of magnitude improvements in performance over the years. Sometimes it doesn’t work worth a damn in which case I just skip the index altogether as you suggested.

    Thank you for continuing to share your knowledge, I have a great deal of respect for what you have to say.

    Reply
    • Bob – the database is freely available from the very first hyperlink in the post.

      I wrote the scripts, and gave them to you. I gave you the database. I packaged the whole thing up in a blog post that’s all completely free for you to read.

      I draw the line at running your own idea queries for you. Put some skin in the game.

      Reply
      • I used the StackOverflow 50gb database, and added 2 stored procedures with index created AFTER for both the nonclustered and clustered approaches.

        Heap is definitely the fastest. But it looks like usp_TempTable_IndexAFTER was able to take advantage of parallelization way better.

        usp_TempTable_Heap
        CPU time = 1154 ms, elapsed time = 752 ms

        usp_TempTable_Index
        CPU time = 9126 ms, elapsed time = 8972 ms
        usp_TempTable_IndexAFTER
        CPU time = 7345 ms, elapsed time = 1681 ms

        usp_TempTable_ClusteredIndex
        CPU time = 6250 ms, elapsed time = 1764 ms
        usp_TempTable_ClusteredIndexAFTER
        CPU time = 7156 ms, elapsed time = 1679 ms

        —————–
        I sometimes put a PK on my temp tables to catch a data error, if I need to and if it doesn’t cost too much in performance… But that’s it.

        Reply
  • Well, I’ve just emerged from a rabbit hole this post sent me down. But it was a good one because I found an tuning opportunity because of this post. In our work (i.e. our Bad™) I see cases where we’re creating temp tables with ID as Primary Key with Identity! Here’s my test proc:

    CREATE OR ALTER PROC dbo.usp_TempTable_Heap2
    @DisplayName NVARCHAR(40) AS
    BEGIN
    CREATE TABLE #MyUsers2 (Id INT IDENTITY (1, 1) Primary key, DisplayName NVARCHAR(40));
    INSERT INTO #MyUsers2 (DisplayName)
    SELECT DisplayName
    FROM dbo.Users;

    SELECT Id, DisplayName
    FROM #MyUsers2
    WHERE DisplayName = @DisplayName;
    END
    GO
    SET STATISTICS TIME ON;
    EXEC dbo.usp_TempTable_Heap2 N’Brent Ozar’;
    SQL Server Execution Times:
    CPU time = 983 ms, elapsed time = 364 ms.

    I found that if I compared that with yours I’d see

    CPU time = 1062 ms, elapsed time = 299 ms.

    so a 65ms difference. Not earth shattering but better, but maybe even more important I read “be ruthless about filtering as early as possible” a few times and booyah I started seeing opportunities to improve our (anti)pattern above. In fact, why generate an identity when the table you’re selecting from already has one! And I see other places where we put fields into a temp table and honestly, they’re not even used (maybe once they were, but not presently). Good times.

    Thank you Brent! Off to refactor-town!

    Reply
  • Wojciech Sawicki
    August 20, 2021 6:00 am

    Thank you Brent
    I have learned a lot.
    I am just proposing to slightly change the rule.
    If you’re only going to access the data once, leave it as a heap.
    On
    If mssql only going to access the data once, leave it as a heap.
    If select will be

    SELECT m.Id, m.DisplayName,p.AnswerCount
    FROM #MyUsers m
    inner join [dbo].[Posts] p on p.OwnerUserId=m.Id
    WHERE DisplayName = @DisplayName;

    and argument =John
    The index version wins.

    Reply
    • You’re welcome, Wojciech. Now, rather than just saying something wins, write out the full repro like I do above with the T-SQL and the statistics for each version. You wouldn’t just want to make unproven statements, right? Thanks.

      Reply
  • Varun Deshpande
    August 20, 2021 8:47 am

    The devs at my friend’s office use temp tables so much that he ended up telling them not to create any indexes on the base tables as no queries run on the actual tables, lol. Needless to say their tempdb was in terabytes.

    Reply
  • A true story:
    In a long proc, the optimiser recommended adding an index on a temp table. I vividly recall the results: exactly the same length of time to run the two versions, the one with the Index took even slightly longer. Thanks – not only for the explanation, but also for providing other possible solutions.

    Reply
  • my main use of temp tables is to prevent multiple sorting or complex queries;

    WITH myData as(select things)
    SELECT t1 from myData d1 join myData d2 on d1.it = d2.that

    and then note that the myData sub-query is always run twice 🙁
    It is much worse when you have a second step

    WITH myData as(select things),
    myData2 as (select t1 from myData d1 join myData d2 on d1.it = d2.that)
    SELECT allTheThings from MyData2 d1 join myData2 d2 on this=that

    will run the MyData subquery 4 times.

    Reply
  • Alex Friedman
    August 23, 2021 2:45 pm

    Ha, I was sure you were going to talk about recompilations. But yeah, totally. Show people how to do performance tuning on a specific case by breaking apart a huge query, materializing the most selective filtering first into a temp table — and next thing you know, they’re splitting up every query into temp tables stages because the DBAs said that’s the way to do performance tuning! Explain why that doesn’t work, and here come the monster queries again. Circle of life.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu