June 7, 2002 at 7:56 am
What is the life span of a Local Temp Table?
I am doing performance testing using SQL Profiler for a Sales Application. Some Stored Procedures that use local Temp Tables are taking a while.(i.e. StartTime and EndTime in Profiler)
June 7, 2002 at 8:16 am
Should be the lifetime of the connection or of the proc, depending on how used. If you create one inside a proc it gets dropped on exit (though I code for explicit drop). If you're doing it via sql from the client app, it will get dropped when the connection closes unless you do it sooner.
Andy
June 7, 2002 at 8:32 am
If you are trying to speed up your application then find a way to do the work without using a temp table. I have found that people often resort to using a temp table with a cursor instead of running a more complex to write joined select or update statement. If you can figure out a way to handle a job in a single statement it will be much more efficient.
I see this alot with triggers. Most assume that you need to put all the information from inserted or deleted into variables in order to validate a users input... When all you need to do is one update statement that is joined to inserted.
June 7, 2002 at 12:44 pm
what i need to know is when that stored proc ends, does SQL Server 2000 then take up much time to destroy this temporary table object, thus making the stored procedure wait a while before it completes???
June 7, 2002 at 7:42 pm
Actually it has no effect on the stored procedure. The table will remain available as long as the connection remains live. This however creates a problem if you want to reuse the table for any other reason or a new table with the same name but can be a bennifit if you want to work with the results in that table as you do not have to recreate thus saving some overhead. When the connection drops though, the table will be dropped at that moment.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 7, 2002 at 8:08 pm
From BOL:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Andy
June 7, 2002 at 8:26 pm
Thanks Andy I for some dumb reason had my thought in reverse. Guess time to hit the bed. Sorry.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 7, 2002 at 9:39 pm
June 10, 2002 at 2:39 pm
I am wondering if the local Temp table destruction takes a while depending on system load, thus delaying SP:Completed END TIME.
June 10, 2002 at 8:26 pm
Normally and to the best of my knowledge a temp table drops almost instantly simple because the data is stored in tempdb and a pointer is associated as the temp table. When you drop it doesn't do anything with the data except sort of forget it was there unless and acts similar to using truncate table.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 11, 2002 at 11:51 am
is there any literature on this?
June 11, 2002 at 6:40 pm
Unfortunately I have seen no documentation on this, that I can point you to. I learned the last from watching TempDB in profiler when a temp table was created and destroyed both large and small. Also with transactions to update and insert data to the temp table. If I come across anything official I will let you know.
This should have read as "of acts similar to using truncate table or drop table". The space is still there and the data still exists the references to it are just gone.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/11/2002 6:42:34 PM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply