December 5, 2012 at 9:19 am
Is that a good practice to drop all temp tables at the end of a stored proc?
December 5, 2012 at 9:22 am
It doesn't hurt anything.
I don't bother, personally, but there's no disadvantage to it that I know of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 5, 2012 at 9:31 am
GSquared (12/5/2012)
It doesn't hurt anything.I don't bother, personally, but there's no disadvantage to it that I know of.
If you have a proc that is frequently called and it has 10 temp tables, with 20K rows each - is there a chance that due to connection pulling (SPIDs being reused) some of this data would not get cleaned up?
December 5, 2012 at 9:31 am
What's odd, was that I was in a session held by Microsoft about 2 years ago and the expert talking about performance recommended against it. Something about how the structure of the #temp table being preserved for use later if the regular garbage collection did the #temp table cleanup vs the drop statement.
Something to do with this stuff I believe
http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
Look for the section labelled
"CREATE and DROP, Don’t"
I don't know that I worry either way, but it is food for thought, if I can find Pauls' follow up blog about it I'll link it here too
December 5, 2012 at 9:34 am
Lexa (12/5/2012)
GSquared (12/5/2012)
It doesn't hurt anything.I don't bother, personally, but there's no disadvantage to it that I know of.
If you have a proc that is frequently called and it has 10 temp tables, with 20K rows each - is there a chance that due to connection pulling (SPIDs being reused) some of this data would not get cleaned up?
No. Not a problem.
You can test this for yourself by looking in tempdb on your production server. Check the tables periodically, and you'll see them being created, destroyed, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 5, 2012 at 9:36 am
aaah duh.... figured out how to search...
here it is.
Of course he counterracts his own "don't" statement from before and says it shouldn't matter. Which is what I thought until I had the lecture. I'll go back to not caring either way now.
http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
When I heard it in the lecture I was a bit surprised, again, it was my practice to let SQL handle the cleanup, but generally as I developed the SQL that would become a stored proc, I would typically drop my #temp tables.
December 5, 2012 at 11:53 am
I always have a Cleanup section at the end of my procedures to remove temp tables and the like, even if they will be disposed of automatically after the procedure completes. The biggest reason is on occasion I have to run the guts of the procedure manually for one reason or another, and it's just simpler having the DROP statements there in-case I have to run something multiple times.
Also coming from an old-school application development background I'm just used to tying-up these loose ends in code. I mean nothing like having an application that opens a dozen connections to the database without closing them 😀
So definitely, dropping them isn't needed as far as the sproc goes, but for me it's just something I do.
December 7, 2012 at 6:31 am
samalex (12/5/2012)
I always have a Cleanup section at the end of my procedures to remove temp tables and the like, even if they will be disposed of automatically after the procedure completes. The biggest reason is on occasion I have to run the guts of the procedure manually for one reason or another, and it's just simpler having the DROP statements there in-case I have to run something multiple times.Also coming from an old-school application development background I'm just used to tying-up these loose ends in code. I mean nothing like having an application that opens a dozen connections to the database without closing them 😀
So definitely, dropping them isn't needed as far as the sproc goes, but for me it's just something I do.
When I'm writing a script, to either create or debug a proc, I always put a conditional drop at the top:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T (table def);
For that same kind of "run it multiple times to debug/refactor" kind of thing. I put it before, because that way I can continue to query the temp tables after the script finishes, if I think I need to see what's in them at that point.
Allows the multiple-run thing, but I don't have to remember to select just the part of the script I want (to avoid the drop commands if I don't want them).
Either way works just fine, of course. Just comparing similar habits at this point.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 7, 2012 at 9:26 am
I had an 'analyst' once who would create dozens of temp tables with huge amounts of data in them, never drop them, and leave his sessions open for days. It caused me problems as it would hold up large portions of the temp db's allocated space. For that reason I always strongly suggest people drop their temp tables as soon as they are no longer needed, or use a temporary 'real' table in a filegroup on larger/faster disks instead.
December 7, 2012 at 7:12 pm
sestell1 (12/7/2012)
I had an 'analyst' once who would create dozens of temp tables with huge amounts of data in them, never drop them, and leave his sessions open for days. It caused me problems as it would hold up large portions of the temp db's allocated space. For that reason I always strongly suggest people drop their temp tables as soon as they are no longer needed, or use a temporary 'real' table in a filegroup on larger/faster disks instead.
Heh... DROP USER works real good about the 3rd time they don't listen. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply