Posted on

Limited Giveaway – The Case of the Slow Temp Table: A Performance Tuning Problem

Update: this giveaway is closed, all seats were given away. Thanks!

I have a new course that I’d love to test out on a few willing students!

I’m giving away a limited amount of one-month free memberships for “The Case of the Slow Temp Table: A Performance Tuning Problem.”

If you’re willing and able to take the course and give me feedback (just a review or survey), grab a seat here (while they last).

The Case of the Slow Temp Table: A Performance Tuning Problem (50 minutes)

Why would using a temp table cause a stored procedure to slow down dramatically and use massively more logical reads, compared to a permanent table?

In this course…

  1. Watch a demo of weird temp table performance problem in SQL Server
  2. See how to measure the problem
  3. Try your hand at figuring out ways to speed up the temp table
  4. Then watch me explore the issue and see a couple of possible workarounds to make that temp table faster.

The problem shown in this course applies to SQL Server 2005+. The demo code has been written and tested against SQL Server 2016 and SQL Server 2017 CU1.

Each video has closed captions in English. A written transcript is included with each lesson.

Preview of the First Lesson – The Case Begins

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 next.

 

Leave a Reply

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