Eliminate the Use of Temporary Tables For HUGE Performance Gains

An Example: Rewriting A Stored Procedure Using Temp Tables 

For this example, we will use the Northwind database. The problem we want to solve is that we need a listing of categories and products in the Northwind database, with a column stating how many products are in each category. This is based on a real case where I needed the count of products within each category. For the curious, the reason was so that I could create JavaScript dynamically to populate a second listbox on the fly.

The desired output looks something like this:

This result says that for the category beverages, there are two products.

The first query that follows, which is designed to produce the required results, is slow and uses a temporary table.


SELECT GETDATE()

GO

-- CREATE OUR TEMPORARY TABLE

CREATE TABLE #Temp_Example ( 
     [CategoryID] INT NOT NULL, 
     [Category_Count] INT NOT NULL
)

-- INSERT THE VALUES WE LATER NEED INTO THE TEMP TABLE

INSERT INTO #Temp_Example (CategoryID, Category_Count)
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CATEGORYNAME

-- JOIN ON THE TEMP TABLE TO GET OUR VALUES

SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Example.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID
ORDER BY C.CategoryName

-- DROP TEMPORARY TABLE

DROP TABLE #Temp_Example

GO

SELECT GETDATE()


Now, let's take a look at a query that produces the same result, but does not use a temporary table, but instead uses a derived table.

SELECT GETDATE()

GO

-- NOTE HOW WE SIMPLY JOIN ON THE TABLE CREATED IN MEMORY BASED ON THE CATEGORY ID

SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN ( 
            SELECT C.CategoryID, COUNT(*) AS Category_Count
            FROM Categories C 
            INNER JOIN Products P ON C.CategoryID = P.CategoryID
            GROUP BY C.CategoryID, C.CategoryName 
            )CT ON C.CategoryID = CT.CategoryID
ORDER BY C.CategoryName

GO

SELECT GETDATE()


The first query’s performance is dramatically improved with little effort by using a derived table, as demonstrated in the second query.

Here are the steps we took to improve the queries performance:

1. We took the SELECT query from our SELECT INTO query, and put it in parentheses followed by a table name.
2. We joined on the table now in memory, rather than a temporary table.
3. We changed the name of the column selected in our SELECT statement to the name of the column in the nested query.

As you can see, using derived tables is a great way to solve complex business problems!

Give It a Test

I recently read the chapter on advanced queries in the book, Professional SQL Server 2000 Programming, and it states that if your result is going to be large, then it might be better to create a temporary table with an index, because derived tables do not have an index. Don’t let the last statement scare you, I’ve seen a few queries go from using temporary tables or cursors to derived tables and the performance was increased by 50% or greater. As always, you will never know until you test!