This month we have a new host, which I am grateful for. So many people have stopped blogging that it’s a challenge to keep this going. Jeff Taylor has an invite asking us about a core T-SQL topic that can affect the performance of your app and your server: temp tables.
This is an interesting one for me, as I’ve changed my mind on these over the years, especially as Microsoft has made improvements to the SQL Server storage engine and query processor that have helped tempdb to perform better.
They’ve also added things that can impact and load tempdb.
If you want to host a T-SQL Tuesday, ping me. I’m always looking for new (or returning) hosts.
My Answer
My answer to Jeff’s question of temp tables as friend or foe is yes.
They are friends.
They are foes.
Most things that we struggle with in database work are tradeoffs. We have to balance the demands. It’s why we say “it depends” so often because we have to find a way to do more of one thing, while accepting less of another.
If I use temp tables in a query, I can potentially run a query to get a smaller data set that I can query with to get the results I need. I can reduce the memory grant, which might be required if the initial query scans a lot of data.
Suppose I have a 100mm row table. If I am doing a complex join of this data on unindexed columns with multiple other tables, perhaps I want to do something like this (delivereddate might not be indexed):
select c.customerid, o.orderid, o.delivereddate, oho.shipperid, o.salespersonid, ,qty, oh.price
into #limitedorders
from orderheader oh inner join orderdetail od on oh.orderid = oh.orderid
where customerid = 12
This can get me a filtered list of things, which I can then join this temp table with the customer, shipper, salesperson, and other tables, which will be a smaller, quicker join.
However, a temp table can be a problem as well. If you have a query that does something like this:
select *
into #orderlist
from orderheader oh inner join orderdetails od
on oh.orderid = od.orderid
where oh.orderdate > dateadd(year, –10, getdate()
And then you have some sort of query that aggregates across the years.
select c.customername, sum(t.qty * t.price)
from #orderlist t
inner join customer c
on t.customerid = c.customerid
where customerid = 12
I’ve done a lot of work in the first query to gather data, allocate space in tempdb, copy this over, use memory, etc. Then I am going a simple query to aggregate things, and filtering it. In this case, the developer likely followed a pattern of gather data, then sum it from other queries. It might have worked for them, but if I have 1mm orders a year, this will suck up resources.
My Advice
My advice is like Jeff’s. In general, try to work with a query to solve your problem. Use joins, beware of views, and make sure you’ve indexed well. Use CTEs to break down your problem, and don’t use temp tables.
If you struggle to get a single query, or you have poor performance because of memory grants or other issues, then think about shrinking your dataset down using a temp table and indexed columns. Then join this to get other data that you need.
