Transcript

This is the Case of the Slow Temp Table: A Performance Tuning Problem. I’m Kendra Little from sqlworkbooks.com. This problem is about temp tables.

This problem was inspired by a really interesting question I got from some readers.

They said, “We’ve got some code and we don’t understand why it’s slow when it uses a temp table.”

We can take the same code that does updates against a temp table and if we swap in a quote “real table” – a table in a user database that’s not a temp table– it becomes much faster and it uses a lot less IO.

When this code uses a temp table, why is it slower and why is it doing so much more IO?

I was immediately intrigued…

because, well, this temp table, it shouldn’t be slower. I took a look and I found that this was a really, really interesting performance problem.

You might wonder, why would we be updating rows in a temp table?

There’s batch processing of different varieties that may use this pattern. Maybe there’s a large amount of rows that we need to apply a set of business rules to. We want to take the rows that we need to do work on, throw them in a temporary object, because we don’t want to store this information permanently, and then iterate. As we apply different business rules, we update the rows in the table as to their current status.

So there are patterns where it’s useful to update groups of rows in a temporary object, right? We could use a user database object, but if we don’t want to keep it forever, this idea of using a temp table for it is quite attractive.

This pattern doesn’t strike me as being really really weird, this is something that I’ve seen similar things to this in a lot of places and it can naturally happen.

Let’s take a look at this problem query.