Temp tables vs Permanent table with deletes
in [Programming SQL]
|
From: tshad on 5 Apr 2010 17:12 "tshad" <tfs(a)dslextreme.com> wrote in message news:OiSCsHQ1KHA.224(a)TK2MSFTNGP06.phx.gbl... > > "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. >> Also, I didn't think Temp Tables caused recompiles. I was looking at this article and it seems to say yes and no. It seems to say if your creates are at the top of the stored procedure, the temp tables will be **************************************************************************** Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code. The reason this happens is that the temporary objects due not exist when the initial compilation of the code takes place, so SQL Server has to recompile the stored procedure during execution. This recompilation takes place after the temporary object is referenced for the first time. ***************************************************************************** By referenced, does he mean at creation time (I assume this is the case). **************************************************************************************** By placing all of your temporary table creation statements together, SQL Server can create plans for those temporary tables when one of them is referenced for the first time. This recompile will still take place during the execution of the stored procedure, but you have cut down the recompiles from n to two (one for the stored procedure and one when the first reference to a temporary table is made). **************************************************************************************** Here it seems to imply that the recompiles will happen each time the Stored procedure is run - when the table is referenced. ************************************************************************************************* SQL Server will also be able to reuse the execution plan for the stored procedure the next time the procedure is called and your recompiles will go to zero. Remember, like permanent objects, if you change the schema of a temporary table, the change will cause the stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table statements and before you reference any of the temporary tables. If you take the stored procedure created during the section on using Profiler and modify it as written below, you will stop the unnecessary recompiles. ************************************************************************************************ Here it says it won't recompile. But I assume that is as long as the CREATE happens before DML (SELECT,INSERT etc) statement. What about a DECLARE statement? I assume it wouldn't since the DECLARE is not a DML statement. Thanks, tom >> 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 >>> >>> > >
From: m on 5 Apr 2010 20:04 In this case Tony, I think that this is an unnecessarily harsh response: While Celko is reminding us all _again_ about the difference between rows and records, his advise to the OP is sound: for a tiny table (up to 1000 rows), a temp table or a table variable will not only perform as well or better, but will also be much easier to maintain then a 'queue' table. If it is possible, it should perform even better if it is possible to use find these rows from a CTE rather then realizing them at all, but the OP hasn't given enough information to decide if that is possible. In my experience, the best performance improvements come from changing applications not to _need_ databases to do things that they aren't good at; and the second best from fixing schema. The worst 'improvements' come from doing things like avoiding the table creation overhead by using a persistent table for my temp storage - something surprising to many programmers who are used to CPU bound tasks that _always_ run faster if I do less work here ;) "Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message news:#Jsd7rP1KHA.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. > > 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: Tibor Karaszi on 6 Apr 2010 01:20 You can know for sure by executing the proc while catching recompile events in Profiler. then you will know if *your* proc causes re-compiles. Note that as of 2005, there are both recompile events at SQL: level and RPC: level. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "tshad" <tfs(a)dslextreme.com> wrote in message news:#1w32SQ1KHA.348(a)TK2MSFTNGP02.phx.gbl... > > "tshad" <tfs(a)dslextreme.com> wrote in message > news:OiSCsHQ1KHA.224(a)TK2MSFTNGP06.phx.gbl... >> >> "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. >>> > > Also, I didn't think Temp Tables caused recompiles. > > I was looking at this article and it seems to say yes and no. > > It seems to say if your creates are at the top of the stored procedure, > the temp tables will be > > **************************************************************************** > Stored procedures will recompile if the developer has place interleaving > Data Definition Language operations with Data Manipulation Language > operations. This is usually caused when temporary objects are created and > referenced throughout the code. The reason this happens is that the > temporary objects due not exist when the initial compilation of the code > takes place, so SQL Server has to recompile the stored procedure during > execution. This recompilation takes place after the temporary object is > referenced for the first time. > ***************************************************************************** > > By referenced, does he mean at creation time (I assume this is the case). > > **************************************************************************************** > By placing all of your temporary table creation statements together, SQL > Server can create plans for those temporary tables when one of them is > referenced for the first time. This recompile will still take place during > the execution of the stored procedure, but you have cut down the > recompiles from n to two (one for the stored procedure and one when the > first reference to a temporary table is made). > **************************************************************************************** > > Here it seems to imply that the recompiles will happen each time the > Stored procedure is run - when the table is referenced. > > ************************************************************************************************* > SQL Server will also be able to reuse the execution plan for the stored > procedure the next time the procedure is called and your recompiles will > go to zero. Remember, like permanent objects, if you change the schema of > a temporary table, the change will cause the stored procedure to recompile > as well. Make all schema changes (such as index creation) right after your > create table statements and before you reference any of the temporary > tables. If you take the stored procedure created during the section on > using Profiler and modify it as written below, you will stop the > unnecessary recompiles. > ************************************************************************************************ > > Here it says it won't recompile. But I assume that is as long as the > CREATE happens before DML (SELECT,INSERT etc) statement. What about a > DECLARE statement? I assume it wouldn't since the DECLARE is not a DML > statement. > > Thanks, > > tom > > > >>> 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 >>>> >>>> >> >>
From: Tony Rogerson on 6 Apr 2010 01:53 Hi Tom, > How does that work? > Statements using the # table are recompiled. > 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? > The statement using the # table is recompiled - even in a stored procedure. > Or am I wrong here. > > Is the plan created during execution? > Yes - the plan is created during execution, but on hitting a # table it will recompile individual statements taking into account the statistics on those # tables. > Are statistics created as the rows are added to the table? To the # table - yes. Tony. "tshad" <tfs(a)dslextreme.com> wrote in message news:O6NIA#P1KHA.220(a)TK2MSFTNGP06.phx.gbl... > > "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: Tony Rogerson on 6 Apr 2010 02:02
> In this case Tony, I think that this is an unnecessarily harsh response: Given --celko--'s attitude over the past decade on this forum any harsh response is reasonable - in fact, justified. > and records, his advise to the OP is sound: for a tiny table (up to 1000 > rows), a temp table or a table variable will not only perform as well or > better, but will also be much easier to maintain then a 'queue' table. If > it is possible, it should perform even better if it is possible to use > find these rows from a CTE rather then realizing them at all, but the OP > hasn't given enough information to decide if that is possible. > That's not in my experience; for the table expression - derived table or CTE the optimiser hasn't a real idea in most cases (given real world SQL) how many rows will be returned on the intermediary steps, so you get a general plan. Table expressions are literally inline macros, the SQL is expanded into the main query - there is no encapsulation benefits in using table expressions because of this expansion. # table have statistics - it is my experience - and I've a hell of a lot of it in different industries, situations, scale and schema scenarios that a # table is 80% of the time the right choice because of those statistics. CPU is cheap so a plan compile costs milliseconds - probably not even measurable, however getting the plan wrong can cost significant amounts of CPU and disk IO because of the additional amounts of data having to be processed. > In my experience, the best performance improvements come from changing > applications not to _need_ databases to do things that they aren't good > at; and the second best from fixing schema. The worst 'improvements' come > from doing things like avoiding the table creation overhead by using a > persistent table for my temp storage - something surprising to many > programmers who are used to CPU bound tasks that _always_ run faster if I > do less work here ;) Using a permanet table to act as a temporary table just causes blocking, fragmentation and additional logging because people usually locate it in an application database rather than tempdb. In my experience the best performance improvements come from teaching people to think in Sets; often people think the database isn't the right tool for the job because they don't understand Relational Theory so just poo poo the relational database. Tony. "m" <m(a)b.c> wrote in message news:uTLCuyR1KHA.224(a)TK2MSFTNGP06.phx.gbl... > In this case Tony, I think that this is an unnecessarily harsh response: > While Celko is reminding us all _again_ about the difference between rows > and records, his advise to the OP is sound: for a tiny table (up to 1000 > rows), a temp table or a table variable will not only perform as well or > better, but will also be much easier to maintain then a 'queue' table. If > it is possible, it should perform even better if it is possible to use > find these rows from a CTE rather then realizing them at all, but the OP > hasn't given enough information to decide if that is possible. > > In my experience, the best performance improvements come from changing > applications not to _need_ databases to do things that they aren't good > at; and the second best from fixing schema. The worst 'improvements' come > from doing things like avoiding the table creation overhead by using a > persistent table for my temp storage - something surprising to many > programmers who are used to CPU bound tasks that _always_ run faster if I > do less work here ;) > > > "Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message > news:#Jsd7rP1KHA.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. >> >> 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. >> |