June 17, 2011 at 8:14 am
When designing a database I often hear it is not a good idea to use temporary tables. The recommended solution is derived tables, but in many cases these just won’t accomplish the job. Is it better to build normal tables and then truncate them each time or is this worse. What alternatives are there to temporary tables have better performance but similar functionally
June 17, 2011 at 8:21 am
It all depends on what you are trying to achieve i use temp tables all the time and it really does not affect performance that much.
if your results are only a few rows say 100 then look at using a table variable instead of a #temp table.
syntax
declare @customer table (CustomerID varchar(10) , Forename varchar(50))
***The first step is always the hardest *******
June 17, 2011 at 8:33 am
MarvinTheAndriod (6/17/2011)
When designing a database I often hear it is not a good idea to use temporary tables. The recommended solution is derived tables, but in many cases these just won’t accomplish the job. Is it better to build normal tables and then truncate them each time or is this worse. What alternatives are there to temporary tables have better performance but similar functionally
As much in this area the correct answer is: it depends. Database design per-se shouldn't include the design of either temp or derived tables - those are application level resources.
Having said that, a derived table is nothing but a sub-query a.k.a. "in-line view" and therefore the "creation" and further access to it does not requires I/O - everything happens in memory. On the other hand a temp table requires I/O at access time - not taking here into consideration the possiblity of data being already buffered.
Question is, is the derived/temp table going to be read only once on the query or it has to be read multiple times? Depending on the situation and particular scenario a temp table might be better.
In my case, when in doubt I experiment both solutions and chose the one that performs the better in the scenario at hand.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 17, 2011 at 8:41 am
Its always fun when I get to use this statement.. It depends..
What you are trying to accomplish in a given sproc largely drives the choice. CTEs are good for recursive queries and are good for what I would call temporary views. Table variables are good for recordsets that are not very large, meaning not 50,000 records or fewer really WIDE records. Temp tables are good for larger sets.
However, with all that said, any given use case could mean one gives better performance overall.
I will give an example. A number of years ago I had a process that was originally written using temp tables, because it had overall large recordsets. As a test I changed them all to table variables and re-tested, performance was slighly worse. I was curious so I added so internal timers on each version and re-ran my tests. What I found was that the front section which had generally smaller recordsets ran much worse with temp tables but the back half much better. What I ended up with was a hybrid, where the first half is table variables and the last half is temp tables. At the operation when I copy from table variable to temp table I put in a LONG comment explaining why we were doing this so that someone coming along later wouldn't just think they were doing us a favor and changing them all to one or the other. Even with the copy operation which took about 4 seconds, the sproc typiclally ran 50% of the time it took for either temp tables for table variables which ran about 20 minutes.
CEWII
June 17, 2011 at 8:44 am
I agree with Paul in that "It Depends".
Just to add a my two cents to the discussion:
Concrete tables used in a temporary way and truncated before and/or after each use are a common design choice. What I see a lot of people fail to consider however is that if you create said tables in a database running in FULL recovery mode you'll take a penalty over using tempdb which is always in SIMPLE mode.
If creating concrete tables is chosen consider using a non-tempdb database that is running in SIMPLE recovery mode...hmmm, that setup sounds a lot like what tempdb provides 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 10:30 am
When I hear someone say that they have been told not to use temp tables, it usually means they are abusing temp tables and building convoluted processes to get their results.
I see this all the time - in fact. Where the developer has created a query that builds a temp table as the 'first' step in the process. Then, they perform 2 or 3 update statements to update the data in the temp table they just created.
Then, they build another temp table (step 2) from the first temp table and a few more tables joined in to further filter the data. Then, they update a couple more columns, and so on...in some cases I have seen things like this with 10+ steps.
And finally, they create the 'final' temp table - which is then either used to populate the permanent table with all of the 'converted' output for reporting, or they select from the 'final' temp table for the actual report.
Of course, when they populate this final, final permanent table - it has no valid references back to the source tables. When a change comes in to include additional columns from one of the source tables - the change is much harder to make because they have to touch on so many pieces of code to make it work.
When the DBA sees this - he/she tells them not to use temp tables like this, and what they hear is that using temp tables is 'bad'...not that the way they have built the 'process' is really the problem.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2011 at 10:38 am
opc.three (6/17/2011)
Concrete tables used in a temporary way and truncated before and/or after each use are a common design choice. What I see a lot of people fail to consider however is that if you create said tables in a database running in FULL recovery mode you'll take a penalty over using tempdb which is always in SIMPLE mode.If creating concrete tables is chosen consider using a non-tempdb database that is running in SIMPLE recovery mode...hmmm, that setup sounds a lot like what tempdb provides 😉
Just adding two more cents to the pile 😀 ; the use of concrete truncate-and-load temporary tables implies designer is really, darn, 1,000% sure and has business approval signed with blood that never ever will be concurrence there - meaning, not two or more transactions will need such table at the same time.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 17, 2011 at 10:42 am
PaulB-TheOneAndOnly (6/17/2011)
opc.three (6/17/2011)
Concrete tables used in a temporary way and truncated before and/or after each use are a common design choice. What I see a lot of people fail to consider however is that if you create said tables in a database running in FULL recovery mode you'll take a penalty over using tempdb which is always in SIMPLE mode.If creating concrete tables is chosen consider using a non-tempdb database that is running in SIMPLE recovery mode...hmmm, that setup sounds a lot like what tempdb provides 😉
Just adding two more cents to the pile 😀 ; the use of concrete truncate-and-load temporary tables implies designer is really, darn, 1,000% sure and has business approval signed with blood that never ever will be concurrence there - meaning, not two or more transactions will need such table at the same time.
Agreed...I have seen the technique taken to the level of the absurd where a SPID column was added to the "concrete temp tables" to deal with that possibility...talk about re-inventing the wheel 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 6:58 pm
MarvinTheAndriod (6/17/2011)
When designing a database I often hear it is not a good idea to use temporary tables.
Taken by itself, that's a myth and a fair amount of hear-say. I've used Temp Tables to "Divide'n'Conquer" many a 45 minute query only to have in run in between 1 and 8 seconds. One of my Support Developers recently turned a 60 minute job into a 6 second run using a couple of Temp Tables. In fact, it's generaly quite a bit better to create a Temp Table instead of calling the same CTE more than once in the same query because the CTE will execute more than once just like any view.
Temp Tables also make troubleshooting of complicated procs quite easy because interim results in Temp Tables persist in SSMS.
That being said, I also agree with what the other's said. Overuse generally means something is really wrong anywhere from a horrible DB design all the way to developers that have no clue (and that's when DBA's end up forbidding them instead of teaching how to use them).
So, as said by the others, "It Depends". I'll add "Everything in Moderation" to that. 🙂
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply