x

Temp Table v/s Permanent table for temporary process?

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?

more ▼

asked Jul 05, 2011 at 02:29 AM in Default

avatar image

Sharma
1.3k 88 91 95

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Use of a temporary table makes it obvious to all programmers / dbas that it is a temporary table. Makes for easier maintainability.

more ▼

answered Jul 05, 2011 at 02:39 AM

avatar image

ThomasRushton ♦♦
39.9k 20 49 52

I am hoping some more disadvantages on DB storage data file fragmentation and performance point of view?

Jul 05, 2011 at 02:47 AM Sharma
(comments are locked)
10|1200 characters needed characters left

Good question.

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).

more ▼

answered Jul 05, 2011 at 05:49 AM

avatar image

Blackhawk-17
12k 30 35 42

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 05, 2011 at 05:22 AM

avatar image

sp_lock
10.5k 27 37 37

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 05, 2011 at 09:32 AM

avatar image

Tim
40.4k 39 84 166

(1) Does it will increase file fragmentation ? (2) Does it will create issue if two or more session try to process same application process? (3) Drop and recreate will be faster or slow for permanent table in compare with temp ? (4) select or insert will be faster or slow with permanent table in compare with temp?

Jul 05, 2011 at 10:11 PM Sharma
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x42

asked: Jul 05, 2011 at 02:29 AM

Seen: 3305 times

Last Updated: Jul 05, 2011 at 02:29 AM

Copyright 2016 Redgate Software. Privacy Policy