Output Cache Table

Consider the stored procedure CustOrderHist from the Northwind sample.

CREATE PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

Enter the following query in SQL Management Studio to test the procedure.

EXEC [dbo].[CustOrderHist] @CustomerID = 'ANTON'

The stored procedure will produce a custom result set. The result set contains the total number of products purchased by the customer with ID of ANTON.

Let’s design a table to cache the output of the stored procedure.

The columns of the output cache table will have to match the columns in the custom result set. It will help if all columns in the table allow storing a null value.

We will also include a column to capture the value of the parameter. If you have more than one parameter, then create a column for each of them. This will allow distinguishing cached result sets produced with different parameters.

You may need to incorporate a column capturing the user identity to segregate cached data produced by application users. You don’t have to implement the column if it is acceptable that data cached by any user is shared with other application users.

One more column will be required to register a cache expiration date. We will assign an expiration date to the cached data rows. This will simplify removing the “stale” data from the output cache table.

Let’s name the output cache table after the the stored procedure.

The following script will create the output cache table with the name CustOrderHist_Cache.

CREATE TABLE [dbo].[CustOrderHist_Cache](
    [ProductName] [nvarchar](40) NULL,
    [Total] [int] NULL,
    [CustomerID] [nchar](5) NULL,
    [CacheID] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [CacheUserName] [nvarchar](40) NULL,
    [CacheExpires] [datetime] NULL
) 

This is a brief description of the table columns.

Column	Description	Source
ProductName	The name of the product purchased by customer.	Custom Result Set
Total	The total number of products purchased by customer.	Custom Result Set
CustomerID	The value of the CustomerID parameter.	Stored Procedure Parameter
CacheID	Generic primary key of the output cache table.	Caching Algorithm
CacheUserName	The name of the application user that has caused the caching of data rows.	Caching Algorithm
CacheExpires	The date when cached data rows need to be deleted.	Caching Algorithm

The highlighted columns represent the custom result set produced by the stored procedure and its parameters.  If your own stored procedure or web service returns more columns in the output or requires more parameters, then incorporate them in the design of the table.