October 26, 2009 at 7:08 am
I feel I have learned enough SQL to start really looking at the optimization of my queries. One thing I have found hugely successful so far is breaking down large queries into Temp Table work and then manipulating those tables (especially to get away from subqueries).
In general, what are the big downsides to using temp tables? I'm sure there is no universal answer, but are there query types where temp tables generally aren't a good idea?
October 26, 2009 at 7:43 am
huston.dunlap (10/26/2009)
I feel I have learned enough SQL to start really looking at the optimization of my queries. One thing I have found hugely successful so far is breaking down large queries into Temp Table work and then manipulating those tables (especially to get away from subqueries).In general, what are the big downsides to using temp tables? I'm sure there is no universal answer, but are there query types where temp tables generally aren't a good idea?
Temporary tables require extra resources to create and maintain the data within them. These extra resources come from one of the hardest hit areas on most servers, tempdb. Also temporary tables and the statistics maintained in them can lead to recompiles (although 2005/2008 statement level recompiles are not nearly as painful to the system as 2000 query recompiles were). Recompiles cause quite a lot of blocking when they occur which can lead to other problems.
You just have to evaluate if you're breaking down your queries as a means of solving an issue with performance or because you're hitting a logic problem where you can't quite find the right set-based approach. Using temp tables can make performance better, but they can also make it worse.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2009 at 7:51 am
Since SQL will build a derived table (subquery) in tempdb anyway, if it's big enough to warrant that, I don't usually consider that a significant factor, either positive or negative.
Recompiles, as mentioned, can be a problem with them. This is the main reason to avoid them where you don't need them. However, if the temp table is pretty consistent, it can be cached in RAM and have stats kept on it, and then be re-used by multiple calls for the proc, so the performance hit only happens the first time in that case. That only applies if the table is one page (8k) or less, if I remember correctly.
I tend to use temp tables where I have logic complex enough that it becomes hard to follow if it's all put in one big query, or where it ends up with a poor execution plan if it's in a single query. The other place where they come in really handy is if the data in them will be used more than once in a proc, or if it will need to be passed to a sub-proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2009 at 8:03 am
huston.dunlap (10/26/2009)
One thing I have found hugely successful so far is breaking down large queries into Temp Table work and then manipulating those tables (especially to get away from subqueries).
In general there's nothing wrong with subqueries. It may sometimes be advantageous to break a query down with temp tables and other times it may not. Test both ways and see which works better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2009 at 10:30 am
Temp tables can be good for design flexibility and ease of understanding. Take two situations that both return the correct results: a series of temp tables or a single complex select statement.
If the business logic changes it may be easier to change the temp table solution or it may be (near) impossible to modify the single select statement to return the new desired results.
I have had temp table solutions that merely reflected my inability to write a graceful single select solution. But for the next guy who had to change the code it was probably easier to understand. So there needs to be a balance between performance, flexibility, understandability, etc.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply