October 8, 2003 at 6:25 am
Hi!
In many stored procedures I found some drop #temp_table statements.
On my objection, that you do not need to delete temporary tables,
cause they are deleted automaticaly and it only adds overhead and
number of locks on tempdb to the server, a guy from development said
that automatic deletion of temp tables does the same job, the same
way, the same time.
So is deletion of temporary table from stored procedure is worse or
does not matter?
/* Would appriciate some leads */
Thanks.
Edited by - Roust_m on 10/08/2003 06:27:55 AM
October 8, 2003 at 6:35 am
Although BOL states that
quote:
If a temporary table is not dropped when a user disconnects, SQL Server automatically drops the temporary table
I think this falls into the 'good programming style' category.
Like explicitely writing
Set <MyObject> = Nothing
in VB.
Don't know if it is really necessary, but whether your sproc deletes the tables or leave this to SQL Server, the overhead and the number of locks should be pretty much the same, I guess.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 8, 2003 at 6:54 am
quote:
Don't know if it is really necessary, but whether your sproc deletes the tables or leave this to SQL Server, the overhead and the number of locks should be pretty much the same, I guess.
- Our application heavily uses tempdb. Since most stored procedures are run many times by the same user (connection), would it be better to replace "drop #temp_table" statement by "TRUNCATE TABLE #temp_table" statement, so do decrease the number when such tables are created/dropped?
/* Making "if not exists ..." check, when creating a table, of course */
October 8, 2003 at 7:10 am
Each connection will create its own version of the temporary table. Otherwise, if two users were connected at the same time they would interfer with each other.
If you create a temporary table is two different QA windows and then select * from sysobjects, you can see how SQL Server does it:
NAME
#1__________________________________________________________________________________________________________________00000000062C
#1__________________________________________________________________________________________________________________000000000633
Jeremy
October 8, 2003 at 7:31 am
quote:
Each connection will create its own version of the temporary table. Otherwise, if two users were connected at the same time they would interfer with each other.If you create a temporary table is two different QA windows and then select * from sysobjects, you can see how SQL Server does it:
So when a user opens the application in the morning and closes it at the end of workday, he/she uses the same connection and versions of temp tables, right?
If the user disconects from the server for some reason /* i.e. PC reboot */, then these tables are recreated with "if not exists ... create #temp_table ..." statement.
I do not find any problems in truncating and thus keeping temp tables, instead of dropping them... We just need to owner qualify these tables, to tell one table with the same name from another temp table created by different user.
October 8, 2003 at 8:03 am
quote:
So when a user opens the application in the morning and closes it at the end of workday, he/she uses the same connection and versions of temp tables, right?If the user disconects from the server for some reason /* i.e. PC reboot */, then these tables are recreated with "if not exists ... create #temp_table ..." statement.
I do not find any problems in truncating and thus keeping temp tables, instead of dropping them... We just need to owner qualify these tables, to tell one table with the same name from another temp table created by different user.
Your initial post referred to temporary tables in stored procedures, which are automatically dropped at the end of the procedure. I used to rely upon this, but was advised by someone more experienced that he considers it a "best practice" to explicitly drop any temporary tables created in a SP as soon as possible. He felt that kept things cleaner, particularly if the SP errored out.
Now you seem to be referring to local temporary tables created outside of stored procedures. As Jeremy wrote, you do not need to owner-qualify these tables, as they will be unique by session; besides, who guarantees an owner will be unique withis an application? Your IF NOT EXISTS...CREATE TABLE statement would need to find this same name in tempdb, which is not something I would try... Perhaps you could consider table variables.
--Jonathan
--Jonathan
October 8, 2003 at 3:39 pm
I always put a drop statement right before the creation with a check to see if the table exists. Most users won't run into the problem of having the table exist but I do all the time while I'm debugging in QA without closing the connection. So to me it just saves time to add 2 lines of code to check for existence and if so drop it.
BTW: A shortcut for checking existence is
IF object_id('tempdb..#foo') IS NOT NULL
DROP TABLE #foo
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 9, 2003 at 12:25 am
quote:
Now you seem to be referring to local temporary tables created outside of stored procedures. As Jeremy wrote, you do not need to owner-qualify these tables, as they will be unique by session; besides, who guarantees an owner will be unique withis an application? Your IF NOT EXISTS...CREATE TABLE statement would need to find this same name in tempdb, which is not something I would try... Perhaps you could consider table variables.
Actualy I am having both types of temp tables. Usage of temporary table, created outside stored procedure causes the procedure to always recompile. Does usage of table variables cause procedures to recompile in such case?
October 9, 2003 at 7:24 am
I never heard that referencing a temp table created outside a stored proc causes the proc to recompile evertime the proc is called. That's a new one for me.
Personally I'm with Frank. I prefer to clean up after myself and drop the temp tables at the end of my proc.
October 9, 2003 at 8:01 am
I also have to side with the "best practice" of cleaning up after yourself when it comes to temp tables. It may eat a few extra CPU cycles to perform the drop table command, but as an "old school" programmer I like to know that the memory space occupied by that temp table is available for other work as soon as the command is completed.
-Al
Funny thing about people and their computers... The computer is completing millions of processes every second, but the user still thinks it is too slow.
"I will not be taken alive!" - S. Hussein
October 9, 2003 at 9:02 am
I like to put the drop in myself. Sometimes to debug a procedure, I copy it into QA and comment out the stored procedure specific stuff (changing the params to a declare). I'd have to add the drop statement if it didn't exist to rerun the proc more than once on the QA connection. Also, adding a begin tran and rollback - normally added only in QA - can be handy to test without changing the data too.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
October 10, 2003 at 4:57 pm
quote:
I like to put the drop in myself. Sometimes to debug a procedure, I copy it into QA and comment out the stored procedure specific stuff (changing the params to a declare). I'd have to add the drop statement if it didn't exist to rerun the proc more than once on the QA connection. Also, adding a begin tran and rollback - normally added only in QA - can be handy to test without changing the data too.
That's interesting because I always do just the opposite. When I have a script I want to debug I turn it into a stored procedure and then debug it in Visual Studio or QA.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 23, 2003 at 8:11 am
If you are using SQL2000, you could try the new table datatype. Check out 'Using Special Data' in BOL for an example.
October 23, 2003 at 8:18 am
quote:
If you are using SQL2000, you could try the new table datatype. Check out 'Using Special Data' in BOL for an example.
Can such tables be passed from one stored procedure to another for processing?
October 23, 2003 at 9:28 am
quote:
quote:
I like to put the drop in myself. Sometimes to debug a procedure, I copy it into QA and comment out the stored procedure specific stuff (changing the params to a declare). I'd have to add the drop statement if it didn't exist to rerun the proc more than once on the QA connection. Also, adding a begin tran and rollback - normally added only in QA - can be handy to test without changing the data too.That's interesting because I always do just the opposite. When I have a script I want to debug I turn it into a stored procedure and then debug it in Visual Studio or QA.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
I find it easier to try different methods, get timing (GetTime() can more “realistic” than statistics at times), use transactions to peek at results and rollback, etc. I end up doing a lot of hacking by the time I'm done with some complex queries. Sometimes I like to load a temp table and keep it alive while testing the code that follows. Being able to select text and just run it is very handy. No locals though.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply