T-SQL Tuesday #201 - Temp Tables, Friend or Foe?
T-SQL Tuesday is a monthly blog party hosted by a different community member each month. This month, Jeff Taylor (blog) asks us for our take on temp tables:
So, do you use temp tables, or do you pull them out wherever you find them? Make your case. I’ll be reading every post.
His whole post is worth your time, so please pop over there once you’re done here and check it out.
Of Course, It Depends™️ !
As with almost everything in SQL Server, there is no one-size-fits-all answer when it comes to temp tables. There are times where temp tables have rescued a query, and others where they’ve just caused trouble. And then there’s the times where you’re just scratching your head.
Can you use a CTE or a derived table in place of a temp table? In many cases, yes! But it doesn’t always work out well. When things get particularly complex, the SQL Server optimizer can struggle to find a good plan that doesn’t involve lots of table scans.
The Good
When I first learned about CTEs, I was just itching to find a use for them. Eventually, I found one. We had a query that was taking 10-15 minutes to execute, often with the application timing out. I found the same subquery being used a half-dozen times in the query, so I swapped it out for a CTE. And runtime…didn’t change! I replaced the CTE with a temp table and all the metrics for the query came down significantly, including (and most importantly) the runtime - under 90 seconds. It was part of a daily job that ran overnight, so any more optimization wasn’t worth the time.
Other times, I’ve gone to temp tables when I’ve had multiple derived tables that start from the same table or two, but with slight variations. In these cases, I look to consolidate this into a single pull from the original table into the temp table, indexed if necessary, so that I only have to make one trip through the original large table. The result is a drastic reduction in I/O, sometimes the elimination of some spool operators, and improved runtime.
I’ve reached for temp tables many times where I needed to use the same subset of data multiple times. Temp tables may not be the solution I land on for the final implementation, but it’s usually my first stop.
And sometimes, you just have to loop through 9000 databases to collect global “statistics” for a report and need a place to stash things for a bit.
The Bad
Early in my tenure at one job, I spotted a terrible abuse of tempdb. In a stored procedure, someone had written a select * into #temp from table..., but only used a handful of the two dozen columns it retrieved. When I asked about this usage, I learned that there’s a misconception (no idea where it came from) that there are “optimizations” in SQL Server that make this perform better. If you’re reading this, you probably know this is not true. If you’re ever in need of a reference to back it up, Paul Randal dispels this quite well.
The Ugly
We only have to look back one month for this one. You can use a temp table to hold some reference data in your stored procedure, but…WHY!?
So Now What?
You don’t have to go all-in on temp tables, nor should you. You don’t have to avoid them entirely, nor should you. They can be the solution to some problems, but used improperly they can make things worse.
Experiment and learn what’s right in each situation. For complex logic in SQL, I always try to break it down into smaller, more manageable chunks. Often this isn’t for performance reasons but to make it easier to understand. I find it easier to write those first passes as temp tables, and then later test switching to CTEs or derived tables to see how the performance characteristics change.
Over time, you’ll start finding patterns to when they’re appropriate and when they should be avoided.
One critical bit to be mindful of - if you’re short on space for tempdb and fill it up, everything can stop. If you’re putting multiple gigabytes of data into a single temp table, you might want to rethink your choices.
Special Thanks
I nearly didn’t write this month, but Andy Yun gave me the nudge I needed to get just go for it as kind of a way to take my mind off some other things. So - thanks, Andy!