SQL Server Performance and temporary tables

Yesterday I golbed about SQL Server performance and I thought I’d continue on in that vein today.
Temporary tables where a feature in SQL Server that I tended to avoid. I figured that I should handle everything through code and using subqueries (with IN and EXISTS clauses) or even the new table variables (well… new since SQL Server 2000), but there are excellent efficiencies to be gained by using the tempdb database.
There are two types of temporary table: local (prefixed with #) or the global (##). I haven’t found much use for the latter type, but the former is useful when you are doing repeated tasks, in a stored procedure, with the same information. Instead of querying the data each time with IN or EXISTS you can dump it into a temporary table and then join to that table.
Here’s an example using yesterday’s Adventureworks code. Let’s say I’m writing a stored procedure to do a great deal of work on Products which have a Product Number beginning with FW. I could use this:

USE AdventureWorks ;
GO
create table #productids (productid int NOT NULL primary key);

insert into #productids
SELECT ProductID
FROM Production.Product p
WHERE ProductNumber LIKE ‘FW%’
order by ProductID

select
/* or do something else with */
SalesOrderID
FROM Sales.SalesOrderDetail sod, #productids p
WHERE sod.ProductID = p.productid

/* and now do something else with it */

/* but at the end don’t forget to: */
drop table #productids

This can really cut down on your execution time and you also benefit by making your code more modular and readable.

PS: If you copy this code to fool around with it, don’t forget to replace the singles quotes with normal single quotes as you may be getting strange replacement characters over the internet.

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , . Bookmark the permalink.

2 Responses to SQL Server Performance and temporary tables

  1. tgrignon says:

    Found a good blog posting today which goes into a lot more detail about temp tables:

    http://decipherinfosys.wordpress.com/2007/05/04/temporary-tables-ms-sql-server/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s