Not dropping temp tables is better for performance

I have wondered about this one for some time now but have never got to the bottom of it. Whether to drop temp tables at the end of stored procedures or not, I never thought it really mattered to performance given the beefy machines we get to command these days.

I have asked a few fellow DBAs and the response was usually along the line of “yeah you should drop temp tables, because it is a good practice.” That’s just not good enough for me.

After reading Paul White’s blog post one day, an idea came to mind. I could use sys.fn_dblog to see if dropping temp tables made any differences. I then tried out a simple test and reached a conclusion for my peace of mind.

I will show you the test I have done and the findings. The code is pasted below if you would like to try it out yourself.

-- Create two procs, one with DROP TABLE and one without. use tempdb go create proc dbo.DropTempTable as create table #demo (i int) drop table #demo go create proc dbo.NoDropTempTable as create table #demo (i int) go

Once the procs are created, you can then run the example.

checkpoint -- in order to reset the fn_dblog output
exec dbo.DropTempTable -- Run the first proc
select
    operation,
    [transaction name],
    [transaction id]
from sys.fn_dblog(null, null)
go

checkpoint
exec dbo.NoDropTempTable -- Run the second proc
select
    operation,
    [transaction name],
    [transaction id]
from sys.fn_dblog(null, null)
go

The example above should be self-explanatory. What I want to show you is the output from fn_dblog:

image

As you can see, the transaction log entries were identical except the extra transaction required for dropping the temp table in the first proc. Looking at the result, it is reasonable to conclude that NOT dropping temp table is better for performance because SQL Server has less work to do.

To me that settled it, why wouldn’t I write less code if it is better for performance?

–oo00oo–
For more info, please refer to the following links:
Explicitly dropping Temporary Tables and performance – Myth or Fact?

It took forever to delete one record

Have you ever tried to delete a record from a table but it seemed to have taken forever? Furthermore, you checked everything and just couldn’t find any performance problems with the server.

Well, check again. Check the foreign key references on the primary key of the table. Now you see it? Yes, SQL Server was checking the foreign key constraints on every table big or small that references your primary key table. It would only delete the record(s) after it made sure that your statement would not introduce data integrity issues.

I am now going to use the AdventureWorks.HumanResources.Employee table to demonstrate how you can work around the delete performance issue.

Check table

Assuming you already have the table name in a query window, now highlight the table and press Alt+F1. It is the same as running the “sp_help” system proc on the table.

If you got the following error instead of proper results, make sure you surround the schema and table names using quotes or square brackets.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Like this:

exec sp_help 'HumanResources.Employee'
exec sp_help "HumanResources.Employee"

exec sp_help [HumanResources.Employee]

You will get several result sets and pay attention to the one titled “Table is referenced by foreign key”.

image

Disable foreign key constraints

Copy the list of constraints to a new query window.

image

Replace the beginning of the lines with “alter table “.

image

Replace semi colons with ” nocheck constraint “.

image

Your script should look like the one below

image

By executing the script, you are effectively disabling the foreign key constraints on these table.

Delete the record(s)

Now you should be able to delete the desired records with a much faster response time(assuming you’ve got appropriate indexes and everything).

delete AdventureWorks.Sales.SalesPerson
where EmployeeID =  280

Re-Enable the foreign key constraints

We can re-enable the constraints by using the same script we’ve prepared earlier for disabling the foreign key constraints. We just need to replace “nocheck” with “with check check”.

image

In case if you are wondering why CHECK CHECK, here is the official explanation from Books Online:

The WITH CHECK CHECK CONSTRAINT re-enables the constraint, and also validates the existing data against the re-enabled constraint.

So it should be read like WITH CHECK (pause) CHECK CONSTRAINT.

Finally

I hope you find the above tip helpful. However, a word of warning though, this tactical operation is better reserved for the non production environments.