Category: T-SQL Tuesday

Louis Davidson shares an approach:

Temp tables fit into my query writing process as one of those last ditch efforts to make a query execute fast enough. Of course it would have been harder if I had followed all the rules and added a lot of test cases, but I had a pretty busy week last week and am finishing this pretty late on Tuesday night, so I just gave my opinions.

I like Louis’s strategy. It’s easy to add a lot of complexity to queries out of habit, to micro-optimize performance, or because of a meandering thought process. But many times, taking a step back to think about what could make a query simpler will be helpful.

One thing I’ll cover that Louis didn’t touch on is that performance level is (or should be) a requirement. If you have a query running millions of times a day on a system, then yes, it makes sense to squeeze out every microsecond. But for an ELT job that finishes in 20 minutes and where you have a 6-hour window to get the data loaded, shaving five minutes off of the query’s runtime isn’t that important, especially if you’re in read-committed snapshot isolation or using another form of optimistic concurrency.

This is why I recommend starting with simple and only moving to more complex solutions when you need them. Now, do I always follow my own advice? Err…well, the sign pointing to Boston doesn’t have to go there itself…

Comments closed

Marlon Ribunal compares two techniques:

For this post, I needed to do some research because there is more to #temp tables than simply creating one and using it. As I started digging into the topic, I realized there are a lot of discussions around #temp tables and @table variables, especially around when one might be a better choice over the other.

One of the common arguments is that #temp tables go to disk while @table variables stay in memory. Another argument is that this is a myth. That got me curious about what the actual differences are and when they really matter. Discussions like this almost always starts with where the data is stored (disk vs memory).

Yeah, the “table variables are just in memory” is a myth, at least for normal table variables. Memory-valued user-defined table types do change things, as they don’t use tempdb. But they come with their own set of handcuffs.

Comments closed

Rob Farley explains a position:

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.

I agree very strongly with Rob’s position. In stored procedures, temp tables should be there for a specific reason, a resignation that it is a second- or third-best design decision. But sometimes, you need second-best because first-best doesn’t perform well enough.

Comments closed

Andy Brownsword shares an opinion:

For August’s T-SQL Tuesday ask, Jeff has thrown temp tables to the wolves. Does the community like or loathe their usage?

I’ll predict a few ‘it depends’ responses, and I lean towards Jeff’s position that they’re more of the exception than the rule. I’d argue there’s one particular scenario where this exception becomes exceptional (heh).

I imagine that this topic will get a bit of play this month and that it will be a bit more contentious than some might expect. I do like Andy’s scenario of when temp tables become quite valuable.

Comments closed