You are here

Do You Explicitly Drop Your Temp Tables?

Do you explicitly drop your #TempTables after you are through with them in your stored procedures?  Whenever I'm asked to performance tune a stored procedure and I see an explicit DROP #TempTable at the bottom of the code, I remove it.  Another developer asked me today why I do this.  I just assumed this would be common sense, but I guess not.  

4GL coders were taught to close object references at the end of their code block.  This isn't taught as much these days since most languages have "garbage collection" that handles this for you, generally.  This same rule holds true for stored procedures.  I could provide you with some test scripts using the standard FOO and BAR tables, but just thinking through the logic should be enough to convince you that a specific DROP is not needed.  

Performing a DROP, no matter how small the temp table, still takes some amount of time.  While the DROP is executing the procedure still may be holding locks (depending on your isolation levels, etc) that it may not need to.  Instead, let the proc complete and allow SQL Server to naturally clean up the temporary objects for you.  Remember that #TempTables are scoped to the code block that created the object, as well as any children, and when the procedure execution completes they are destroyed naturally since they are then out of scope.  

One More Reason 

Here's one more reason...many data access utilities and ORMs require the temp tables to NOT be DROP'd at the end of the procedure.  Most people don't know this.  A very popular ORM (which will remain nameless) cannot generate a "model" against a stored procedure that SELECTs data out of a #TempTable.  The ORM "model" is basically a way to map the underlying OO model to your stored procedure inputs and outputs.  The "model generator" reads metadata about the stored procedure to determine what its output result set looks like (column names, order, and data types).  Suffice it to say that natively SQL Server does not provide the necessary metadata to satisfy the ORM model generator, so it barfs.  The vendor even states that #TempTables are not supported, use @TableVariables instead.  Nevermind that this could have drastic performance ramifications.  Please don't think this is just bad ORM vendor code...SSRS has similar problems and the advertised workaround is to SET FMTONLY OFF within the procedure.  This trick also works with the ORM model generator, but only if you do NOT explicitly DROP your #TempTables.  

Again, granted, the performance improvement likely won't be much, but it is still a Best Practice to not explicitly DROP your #TempTables.  In my opinion anyway.  

Temporary Objects

Comments

Comment: 
That's interesting I don't generally drop my temp tables but more out of lazyness than any sort of performance consideration. Does this same rule apply to cursors? I generally use cursor variables when (rarely) working with cursors in SQL Server and I close the cursor when I'm done with it, but is there a performance benefit from not using the deallocate command and rather letting the cursor die a natural death?

Comment: 
I never honestly bothered to test that.  I don't write that many cursors where I'm super-concerned with performance so I never thought of it.  Like most people I use a "template" (SSMS code snippet or copy/paste an existing cursor) which always has the CLOSE and DEALLOCATE.  It would be an interesting test.  

Comment: 
True, super performance tuning I dont generally use cursors either, most cases I use them are for slicking up large requests into batches when pulling data across a linked server. In those cases network performance well overshadows any sort of allocation/deallocation issues so as to make them irrelevant.

Add new comment

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer