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).
Building optimisation boundaries
The flip side of development is troubleshooting, and here’s a great opportunity to lean on temp tables for help.
If you’re hunting a bug and get hit with a wall of text, it might be time for a temp table. These long unwieldy queries can be a lot to process – both mentally, and for the query processor. Identifying a clean point to break the code and stage the intermediary results can help in both instances.
Splitting these and using a temp table to stage the results provides an optimisation boundary – for both you and the query engine.
Complex queries can produce large query plans, where inaccurate row estimates compound and lead to sub-optimal choices, impacting performance. By staging partial results into a temporary table, the scope of each query is reduced with the goal of delivering better row estimates, plans, and in turn improving performance.
Rule of thumb: if you need a fresh brew before approaching a query, consider staging a portion into a temp table ☕
How do we choose where the split should be?
- If the query does heavy filtering on a related table – say Customers before joining to Sales – that’s a solid option
- When there’s a variety of filters but you know one is particularly selective, you can isolate that to remove a potentially worse choice for the optimiser
- For nested queries or CTEs, they’re potential candidates as they’re already segregated from the main query
The key is choosing which of these isolates sufficient complexity within the query (so we divide the effort) whilst allowing a clean separation of the logic (so you don’t need to rewrite the entire query in the process).
It’s different for every query, but once you’ve done a few it becomes more intuitive.
Temp tables aren’t always the answer, but when faced with a large, complex statement, temp tables are a solid starting point for improvement.
I’ll close by noting that there’s clearly many use cases for temp tables, and I’m sure the community will share some surprising anecdotes. Whether it’s supporting greenfield code to stage repeated logic, or building optimisation boundaries to untether legacy code – the key is to use them effectively.
Temp tables shouldn’t be a default, but when a query has become too complex to wrangle reliably as a single statement, introducing them as an optimisation boundary can be exactly the right exception.
Bringing this back to Jeff’s question – are they a friend or foe? Yes. Depending on how they’re implemented.
Andy Brownsword 