Hi, In our application for business process developer create permanent table instead of #table and process temporary data in this table and drop this table again in same scope and some time leave this in database and next time drop and recreate for temporary process.
So I want to know what are the strong disadvantages of using permanent table instead of actual temp table for process the temporary data for same scope?
asked Jul 05, 2011 at 02:29 AM in Default
Use of a temporary table makes it obvious to all programmers / dbas that it is a temporary table. Makes for easier maintainability.
answered Jul 05, 2011 at 02:39 AM
It depends on your system's configuration and usage.
Here's a couple of thoughts on each:
A permanent table may end up with heavy blocking issues as simultaneous use from different sessions is underway. A permanent table's indexes are in place and don't need to be recreated.
Temporary tables live in temp dB and this is a highly accessed area which may lead to I/O contention. Each session has their own version of the table to work from and blocking is almost non-existent for the table as a result.
Depending on the size of result sets you are working with you might consider table variables instead (version dependent).
answered Jul 05, 2011 at 05:49 AM
Things to consider where the staging data is stored, temps at store in the well-know place but the "perm" are actually stored in the DB it self. So if your tempdb is on a different array and isnt overloaded then at could be a reasonable choice..
Depending on the size there shouldnt be too much difference. One decent reason to have a perm table is for reviewing reason, maybe.
I would personally test the performace before has, as things like this differ from system to system.
I know there are pro's and con's to both but I prefer staging tables in a separate database or in the one I am working on. If I am only working with a small dataset I may use a temp table but usually use a CTE if I can get away with it. I don't like adding any additional pressure on TEMPDB if I can help it. As others have pointed out it really all depends on your current need. One of the things I like about a perm table is it also allows you the ability to not have to rebuild that dataset if something in your process breaks further down stream.
answered Jul 05, 2011 at 09:32 AM