Transcript

Here’s a chart summing up the performance differences that we’ve seen.

The user table, and what I’ll call the naked table variable, are fast.

Their durations are much lower than when we run the same code and use either a temporary table, or our update statement against the table variable using a recompile hint.

Similarly, we have massively more IO, massively more logical reads that we’re doing when we have the temp table, or when our update statement – again it’s the table variable– uses a recompile hint.

When I was measuring these, I did try very carefully to make sure I wasn’t falling for “observer effect.”

There are times in SQL Server where measuring the duration of a query can really change how long it takes.

Sometimes if we use actual execution plans, or sometimes if we use set statistics time, we might make it slower by measuring it. It’ll always happen a little bit, but sometimes it can be a lot.

This is why, when I was creating the repro code I put in those simple {measurement queries}, “okay I’m going to measure what time it is now, I’m going to run the update statement, I’m going to check the time again and then tell you what the difference is.”

Just as a very simple way for me to measure the duration that wasn’t reliant on “set statistics time.”

Now that’s not to say– I DO love SET STATISTICS IO ON, I do love SET STATISTICS TIME, IO ON, as performance tuning tools. I don’t want to steer you away from those, but when you are measuring things it can be good to measure them in more than one way so that you can get a sense of: okay is timing this or is tracking the IO skewing my execution run time?

We are going to dive into digging through and getting more information and figuring out, what is related to this being slow and how can I speed it up?

This is a really fun problem to work through though!

I encourage you to check it out yourself, play with the code, and see what more you can learn about why it’s slow and how you might be able to speed up the temp table on your own.

Then dive in and join me for the rest of the videos as I walk through how I explore these and my ideas for speeding them up.