In SQL Server you can use temporary tables to store intermediate results. This is a common used technique to speed up query processing. Recently I came across a problem where the temporary tables were causing the performance degradation. I'm not going into the different temporary tables types, there are good posts on these. I'm going to walk you through a real-life example and show that the most given answer in the SQL Server world holds true; "It Depends".

In this case the desired result was a single stored procedure that would return multiple results based on some or no search criteria. If a user navigated to the page the complete set was returned and only one filter was applied. In the result set there was a row count, a paged result and some distinct fields from the total results to provide additional filtering. Think of these as slicers and dicers you use in Excel. I will demonstrate the general idea using the AdventureWorks database. I'm using the 2008 version here.

DECLARE @t table (id int IDENTITY, salesorderid int)

INSERT INTO @t (salesorderid)

SELECT soh.SalesOrderID FROM Sales.SalesOrderHeader soh

INNER JOIN Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID

WHERE sm.Name = 'CARGO TRANSPORT 5'

ORDER BY soh.OrderDate DESC;

 

-- 1 Get Rowcount

SELECT COUNT(*) AS TotalRecords FROM @t;

 

-- 2 Get Current page

SELECT soh.OrderDate, soh.SubTotal, st.Name, p.LastName, c.AccountNumber FROM Sales.SalesOrderHeader soh

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID

INNER JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID

INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID

INNER JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID

WHERE t.id BETWEEN 1 AND 20

ORDER BY soh.OrderDate DESC;

 

-- 3 Get SalesTerritory values

SELECT DISTINCT st.TerritoryID, st.Name FROM Sales.SalesTerritory st

INNER JOIN Sales.SalesOrderHeader soh ON st.TerritoryID = soh.TerritoryID

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

ORdER BY st.Name;

 

-- 4 Get OrderDate Year values

SELECT DISTINCT YEAR(soh.OrderDate) as OrderDateYear FROM Sales.SalesOrderHeader soh

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

ORDER BY OrderDateYear;

 

In preparation a table variable is filled with the filtered set of ID's and is sorted in the desired order. In the first query we retrieve the row count for the set. The second set is the actual result set the user will see in the application. In this case it's the first page with 20 results. In step 3 and 4 additional results are returned that are used in the application to provide quick filters for the user. When I run this query on my database I get 4 sets with a total of 35 rows. For this I used 25628 reads. That seemed a lot to me. The execution plan for this one showed some missing indexes and some clustered index scans here and there. I added a few indexes. If you are running along, here they are:

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_ShipMethodId ON Sales.SalesOrderHeader(ShipMethodId) INCLUDE(SalesOrderID, OrderDate);

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_Select ON Sales.SalesOrderHeader(SalesOrderId, OrderDate) INCLUDE(CustomerID, SalesPersonID, TerritoryID, SubTotal);

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_Territory ON Sales.SalesOrderHeader(SalesOrderID, TerritoryID)

CREATE NONCLUSTERED INDEX temp_TERRITORY_Name ON Sales.SalesTerritory(TerritoryID, Name);

CREATE NONCLUSTERED INDEX temp_PERSON_Lastname ON Person.Person(LastName)

 

This got my reads down to 900. That's to show you will need a proper indexing strategy. But still, more work could be done. One of the things I tried first was use a temporary table instead of a table variable. But this increased my reads. Although I could add indexes the index creation itself would cause even more reads. And in this example the set is not that big but when I was working with the real set things got ugly pretty quickly.

The last thing I tried was to remove the temporary tables altogether and use a view. My thought was to create a view with the search criteria in the view and maybe index that view. This view could easily be reused by the other sets. The definition of the view and the adjusted queries are below:

CREATE VIEW vSearch AS

SELECT soh.SalesOrderID, soh.TerritoryID, soh.OrderDate, soh.SalesPersonID, soh.CustomerID, soh.SubTotal, sm.Name

FROM Sales.SalesOrderHeader soh

INNER JOIN Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID

GO

 

-- 1 Get Rowcount

SELECT COUNT(*) AS TotalRecords FROM vSearch WHERE Name = 'CARGO TRANSPORT 5'

 

-- 2 Get Current page

SELECT v.OrderDate, v.SubTotal, st.Name, p.LastName, c.AccountNumber FROM

(SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS row_id, SalesOrderID,

TerritoryID, SalesPersonID, CustomerID, OrderDate, SubTotal

FROM vSearch WHERE Name = 'CARGO TRANSPORT 5') v

INNER JOIN Sales.SalesTerritory st ON v.TerritoryID = st.TerritoryID

INNER JOIN Sales.Customer c ON v.CustomerID = c.CustomerID

INNER JOIN Person.Person p ON v.SalesPersonID = p.BusinessEntityID

WHERE v.row_id BETWEEN 1 AND 20

ORDER BY v.OrderDate DESC;

 

-- 3 Get SalesTerritory values

SELECT DISTINCT st.TerritoryID, st.Name FROM Sales.SalesTerritory st

INNER JOIN vSearch v ON v.TerritoryID = st.TerritoryID

WHERE v.Name = 'CARGO TRANSPORT 5'

ORdER BY st.Name;

 

-- 4 Get OrderDate Year values

SELECT DISTINCT YEAR(v.OrderDate) as OrderDateYear FROM vSearch v

WHERE v.Name = 'CARGO TRANSPORT 5'

ORDER BY OrderDateYear;

 

An additional index was needed:

CREATE NONCLUSTERED INDEX temp_IX_SALESORDERHEADER_ShipMethodID ON Sales.SalesOrderHeader (ShipMethodID) INCLUDE (OrderDate,CustomerID,SalesPersonID,TerritoryID,SubTotal);

 

This resulted in 466 reads. More optimizations can be done bit a big win was gained by using a view and just retrieving the data directly instead of using temporary sets.

A big lesson lies in the creation of the temporary objects. If you create a simple table with two columns and just 10 records and you select the table you will only get a few reads. If you create a table variable, insert the real table into the variable and select it again you get a couple of hundred reads. On small sets you won't notice a big difference but on larger sets the difference on cpu, reads, writes and duration is really noticeable.