Let me say for starters that I’m pleased we have temporary tables. They’re tremendously useful, but often get abused. Compared to obvious villains like NOLOCK, cursors, and scalar functions, they’re really quite inane (but any of these features can be used for good – trust me). But temporary tables were the one of these I mentioned in last month’s post about signs of a bad query. If nothing else, using temporary tables probably means you’re writing procedural code rather than set-based queries.
The scenario I want to talk about is using temporary tables to materialise a set of data, ahead of using it in a separate query a moment later. That’s essentially what Jeff Taylor is asking us to comment on.
Temporary tables are fine… really. I use them often.
If I’m writing queries in SSMS, to do some quick debugging of a situation, then I’ll often drop the results into a temporary table.
If I’m dealing with remote queries across a linked server, I’ll often use openquery and make a local version of the result set.
If I’m grabbing a tiny subset of data from large tables, scanning to look for examples of particular scenarios, I might use NOLOCK and temp tables, before re-testing those specific cases without NOLOCK. More on this later (because I know a bunch of you screamed and unfriended me when you read that there’s a scenario I used NOLOCK for).
And of course, I might use temporary tables to look at the current wait stats before waiting a moment and re-querying – because that gap in the query is what makes the time-based logic of wait stats work.
The problem with temp tables (or cursors or scalar functions to some degree) is that people use them as procedural crutches to ensure the correctness of their queries, and never refactor the code to make it less procedural. Imagine you are really confident in a piece of logic that you created using temp tables or a cursor, but you know that you really ought to use more set-based methods instead. Do I mind that you used procedural code to work out what you wanted? No – not at all. But how about you use that code in your testing harness, and come up with a better way. Your procedural code can be used to make sure your logic is correct, but maybe stop running it in production, yeah? Particularly once you have the skills to make something that performs better.
And as much as it frustrates me, there are times when procedural code is just better. Expertise in your domain should mean knowing when some of those ‘rules’ (about procedural v set-based) can be broken.
Let’s quickly consider that scenario with NOLOCK, for example.
Imagine you have a known application bug where sometimes the OrderTotal of an Order becomes different to the sum of the OrderLineAmount values. Shouldn’t happen. But it can. Arguably, Order doesn’t need an OrderTotal value, but for the sake of performance and various other things, it does.
Sure, you can explore options with triggers to try to spot it, but I’d be very reluctant to create a trigger on an existing system. One very real option is to run some hefty scanning queries again the tables involved to check the data. I’m fine about using NOLOCK for this kind of thing, because I don’t need it to be completely accurate. NOLOCK can (and will) give inaccurate results, so I drop the results of my scan into a temporary table (probably a table variable actually), and then run the query again without NOLOCK, filtered to the list I came up with, and now able to seek into just those potentially-problematic orders. To see if any of those rows that popped up were just because of data movement causing NOLOCK inaccuracy, and all while minimising the blocking. Is it perfect? No. Am I doing things that would make someone cringe? Sure. My choice though and it meets the goal I’m after.
When I’m running a query that involves a linked server, I feel like I’m even more at the mercy of the Query Optimizer than usual. I’m always somewhat at the mercy of the QO to be fair, but when linked servers are involved, I’m much less able to provide the QO with metadata, based on limited information about “what’s on the other side”. If the stats are looking like I might get a bad plan (with the potential for it to be way worse than a normal bad plan, and potentially impacting two servers), then temporary tables can be my friend. I can work out what data I’d from the remote server overall, fetch that in a single call (dropping it into a temporary table), and then using my new local copy of the data in my main query. Procedural code can legitimately help when you run the risk of having a bad plan across two servers.
Back in the day, temporary tables were often used as a way of breaking down queries to make them more understandable. And there were even times that the QO would prefer to optimise multiple smaller queries that a single large one, making temp tables a reason for performance. And as there could be stats on the temporary table, sometimes it would just work better.
Still today this is a very real tuning method, but I feel like the QO has improved a lot and there are advantages to be lost by materialising intermediate results. When you see a plan operator pushing out 10k rows of data, it’s better not to just let the engine use those rows, rather than drop them onto disk and pick them up again. Then again, if that same branch appears multiple times, then spooling them could be useful. And a temporary table is basically rolling your own table spool. It’s definitely a real tuning method!
I will always advocate for the power of the QO, and for its ability to see shortcuts to avoid work. But sometimes you really have to give a nod to query writers for creating stuff that the QO is going to struggle with, and either introduce or rip out temporary tables to get the behaviour you want.
And ultimately, I’d rather a query be easy to understand and perform well. I’m not going to rule out temporary tables if I can achieve both of these.
It’s all well and good to be a purist, but pragmatism usually wins.
@robfarley.com@bluesky (previously @rob_farley@twitter)
