Temp tables vs Permanent table with deletes
in [Programming SQL]
|
From: Tony Rogerson on 5 Apr 2010 16:06 Hi Tom, You'll no doubt be getting really bad blocking from the deletes; you'll be getting poor performance because of all the logging that needs to happen because of the way you are doing. The problem with # tables is that they cause statement recompiles, but that isn't necessarily a bad thing - how frequently are the stored procedures called but more importantly are they called by many concurrent users (at the exact same time), if so then using # tables will likely cause RECOMPILE locks. You might want to consider using table variables but only if you have multiple concurrent connections executing the same stored proecdures. Hope that helps. Tony. "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 > >
From: tshad on 5 Apr 2010 16:35 "Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message news:%23Jsd7rP1KHA.264(a)TK2MSFTNGP05.phx.gbl... >> 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. > How does that work? If you are working with a temporary table but the plan is created before or as you are starting the procedure - how does it use statistics? Or am I wrong here. Is the plan created during execution? Are statistics created as the rows are added to the table? Thanks, Tom > 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: tshad on 5 Apr 2010 16:41 "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:umdkr5dpvf50ft73sq09frfodtgldv7b78(a)4ax.com... > 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? > As opposed to what? A derived table as Celko suggests? At the moment, it is a permanent table that 10 or 12 functions use for about 20 seconds then it deletes all the records it created. Not sure if I need a table or not. Looking at the options. One problem we had is that in some cases the rows didn't get deleted as they should have so the deletes take longer than needed. I could just leave the table as is, delete all the current rows and fix the procedures that are not deleting the records (which I would do anyway) and leave the code as is. I was looking at a temporary table because there are 4 indexes on the table, but that may not be necessary if the currect records are deleted. Not sure on that because many people could access the table at the same time and put a large amount of rows in the table at once in which case it may be helpful. But then again, the overhead of the inserts and deletes because of the indexes may outweigh the indexes. The reason I was even looking at a temp table was that it would alleviate some of these issues. Thanks, Tom > John
From: tshad on 5 Apr 2010 16:48 "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:O0mDVsP1KHA.5004(a)TK2MSFTNGP05.phx.gbl... > 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. > The GUID is the user key. Not one created for each row. It is what is used to separate my records from your records for the report and deletes. > 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. > Probably right there. Haven't looked at all the procedures yet. My guess is that the previous person looked at a select statement the table was joined with and put an index on the foreign key. > You should carefully consider your query plans to see if you really need > any index at all for your volatil data. Agreed. The program now may say I need them because there are so many records in the table that didn't get deleted as they should have. >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.). > As mentioned, that wouldn't be the case here. Which was why I was looking at a temporary table. Wouldn't need to worry about the Guid at all. But would add an identity column in that case. > 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). Not using a curser here. Thanks, Tom > > -- > 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 >> >> > >
From: tshad on 5 Apr 2010 16:52
"Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message news:%231qj$tP1KHA.5828(a)TK2MSFTNGP02.phx.gbl... > Hi Tom, > > You'll no doubt be getting really bad blocking from the deletes; you'll be > getting poor performance because of all the logging that needs to happen > because of the way you are doing. > Good point. Hadn't thought about the logging issue. > The problem with # tables is that they cause statement recompiles, but > that isn't necessarily a bad thing - how frequently are the stored > procedures called but more importantly are they called by many concurrent > users (at the exact same time), if so then using # tables will likely > cause RECOMPILE locks. Not sure about that as there are so many procedures that use this table. > > You might want to consider using table variables but only if you have > multiple concurrent connections executing the same stored proecdures. > Maybe, but since there can be around 1000 records in this table, I would be nervous about using a table variable for this. I just refactored a procedure that had a table variable that was taking about 50 seconds to process when I was adding 2400 rows to it. Just by change it to a temporary table, the time went down to 2 seconds. Thanks, Tom > Hope that helps. > > Tony. > > "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 >> >> |