Temp tables vs Permanent table with deletes
in [Programming SQL]
|
From: tshad on 5 Apr 2010 14:32 I am looking at changing about 10 procedures that use a Permanent table for nothing more than temporary storage to load and store various records from a select statement and run a Join on this table to update other records. It then deletes all the records it put into the table. The table is used by multiple procedures at the same time keyed by a Guid it always gets at the start of the routines. There are about 4 indexes on this table. There can be any from 1 to 1000 records involved in this. I was thinking about dropping the table and just using a temp table. When the table is created I would then have to create 3 indexes - would drop one index (the Guid). Then the table would go away when I exit the procedure. I don't think there is any necessity to drop the table (and indexes) as it would go away anyway. But now I don't have to delete the records at the end or create the Guid at the beginning. Anything I am missing here? Thanks, Tom
From: --CELKO-- on 5 Apr 2010 15:19 In the 1960's we would write records (not rows) to a scratch tape and share that tape among several procedures. It saved us the trouble of dismounting the tape. One of the fundamental principles of software engineering is that loosely coupled modules are better than what you have. Look up "data coupling', "functional coupling" and "procedural coupling"in any book on software engineering. YOu seem to have one or more of these design problems. For no more than 1000 rows (not records), why not re-write the code to use a derived table in each procedure? The procedures would become independent, loosely coupled modules. You will probably have to do some more work than just that, but it is a start.
From: John Bell on 5 Apr 2010 15:21 On Mon, 5 Apr 2010 11:32:29 -0700, "tshad" <tfs(a)dslextreme.com> wrote: >I am looking at changing about 10 procedures that use a Permanent table for >nothing more than temporary storage to load and store various records from a >select statement and run a Join on this table to update other records. > >It then deletes all the records it put into the table. The table is used by >multiple procedures at the same time keyed by a Guid it always gets at the >start of the routines. > >There are about 4 indexes on this table. > >There can be any from 1 to 1000 records involved in this. > >I was thinking about dropping the table and just using a temp table. When >the table is created I would then have to create 3 indexes - would drop one >index (the Guid). > >Then the table would go away when I exit the procedure. I don't think there >is any necessity to drop the table (and indexes) as it would go away anyway. > >But now I don't have to delete the records at the end or create the Guid at >the beginning. > >Anything I am missing here? > >Thanks, > >Tom > Hi Tom Creating many temp tables can create a bottleneck on tempdb See http://support.microsoft.com/kb/328551 Have you looked at whether you need a table to hold this? John
From: Tony Rogerson on 5 Apr 2010 16:02 > For no more than 1000 rows (not records), why not re-write the code to > use a derived table in each procedure? The procedures would become > independent, loosely coupled modules. You will probably have to do > some more work than just that, but it is a start. 1000 rows? From which planet did you pluck that figure from? No statistics are held on table expressions like a derived table, performance can be horrendous. Temporary tables (akin to a relvar) are widely used and rightly too in SQL Server, statistics are held on a temporary table so you get the best plan available for what you are doing. Temporary tables are in fact real tables and conform to Codd's rules. What's your problem other than the syntax we use in SQL Server doesn't explicitly follow ISO SQL? --ROGGIE-- "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:506ec56a-e829-447c-9896-ee1613b35a6e(a)g30g2000yqc.googlegroups.com... > In the 1960's we would write records (not rows) to a scratch tape and > share that tape among several procedures. It saved us the trouble of > dismounting the tape. One of the fundamental principles of software > engineering is that loosely coupled modules are better than what you > have. Look up "data coupling', "functional coupling" and "procedural > coupling"in any book on software engineering. YOu seem to have one or > more of these design problems. > > For no more than 1000 rows (not records), why not re-write the code to > use a derived table in each procedure? The procedures would become > independent, loosely coupled modules. You will probably have to do > some more work than just that, but it is a start.
From: Sylvain Lafontaine on 5 Apr 2010 16:03
First, it's not only a question of speed of deleting a full table in comparison to deleting multiple records but also because the temporary tables are created in the TempDB database and therefore, don't pollute the file space of your main databases. You should do so even if you were to keep the same table for all the procedures along with the use of a GUID to separate them. By the way, I don't see the point of using a GUID instead of something like an identity value based on an integer field. Second, you should consider dropping all the indexes as well because you have volatil data and usually, it doesn't make sense to add indexes to volatil data because the amount of work required to create the indexes, updating them, searching them and finally, destroying them will be far greater than the amount of work saved by retrieving a portion of the table using a seek operation. Of course, if you need to retrieve in a single operation 100% of the rows that you have inserted, then the indexes are probably even more useless. You should carefully consider your query plans to see if you really need any index at all for your volatil data. The only one that is probably useful will be the one for the GUID used to separate the records betweent the procedures in the case of a unique, global permanent table. (And in this case, probably that you should use an identity field instead of a GUID.). If you are using a cursor to update your rows one by one, then the conclusion about the need of the indexes might be different (but not necessarily). -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "tshad" <tfs(a)dslextreme.com> wrote in message news:eNdIY5O1KHA.6104(a)TK2MSFTNGP06.phx.gbl... >I am looking at changing about 10 procedures that use a Permanent table for >nothing more than temporary storage to load and store various records from >a select statement and run a Join on this table to update other records. > > It then deletes all the records it put into the table. The table is used > by multiple procedures at the same time keyed by a Guid it always gets at > the start of the routines. > > There are about 4 indexes on this table. > > There can be any from 1 to 1000 records involved in this. > > I was thinking about dropping the table and just using a temp table. When > the table is created I would then have to create 3 indexes - would drop > one index (the Guid). > > Then the table would go away when I exit the procedure. I don't think > there is any necessity to drop the table (and indexes) as it would go away > anyway. > > But now I don't have to delete the records at the end or create the Guid > at the beginning. > > Anything I am missing here? > > Thanks, > > Tom > > |