Blog: Posts from May, 2012

Now it is time to design a caching algorithm that will execute the stored procedure CustOrderHist and store the custom result set to the output cache table with the name CustOrderHist_Cache.

The framework of a Code On Time web application minimizes interactions with the database. For example, if a user has arrived to a page, then the application will count the total number of  rows in the visible data views to figure the number of pages presented in the view pager. Then the application will retrieve the first page of visible data rows. The framework will not try to figure the number of pages again until a filtering condition is changed or data has been modified.

Our objective is to minimize the calls to the stored procedure. The described behavior of the web application framework provides us a very convenient condition for execution of the stored procedure.

The stored procedure will be executed when the application
needs to count the number of available data rows.

It is necessary to populate the stored procedure output cache table only when there is no content or the content has expired. The expiration will be set to 30 seconds.

The following algorithm is written in T-SQL and can be adapted to other dialects of SQL supported by the database server you may be working with.

/*********************************************************
 The application framework will delete the "Debug" section
 from the business rule. The parameter values will be 
 provided by the application framework at runtime.
*********************************************************/

-- Business Rule Parameters: Debug
declare @BusinessRules_RequiresRowCount int
declare @BusinessRules_UserName nvarchar(40)
declare @CustomerID_FilterValue nvarchar(5)

set @BusinessRules_RequiresRowCount = 1
set @BusinessRules_UserName = 'username'
set @CustomerID_FilterValue = 'ANTON'
-- Business Rule Parameters: End Debug

/*********************************************************
 This is the actual business rule implementation 
*********************************************************/

if @BusinessRules_RequiresRowCount = 1 
    and @CustomerID_FilterValue is not null
    and not exists (
        select 1 from CustOrderHist_Cache
        where CacheUserName = @BusinessRules_UserName and 
            CustomerID = @CustomerID_FilterValue and
            CacheExpires > getdate()
    )
begin
    -- delete the data cached by the current user if it has expired.
    delete from CustOrderHist_Cache
    where CacheUserName = @BusinessRules_UserName and 
        CustomerID = @CustomerID_FilterValue
    -- create temporary table to capture the custom result set
    create table #CustOrderHist(
        ProductName nvarchar(50),
        Total int
    )
    -- execute the stored procedure and capture the custom result set
    insert into #CustOrderHist
    exec [CustOrderHist] @CustomerID_FilterValue
    -- copy the custom result set to the Output Cache Table
    insert into CustOrderHist_Cache(
        ProductName, Total, CustomerID, 
        CacheID, CacheUserName, CacheExpires)
    select 
        ProductName,                    -- column from #CustOrderHist
        Total,                          -- column from #CustOrderHist
        @CustomerID_FilterValue,        -- stored procedure parameter
        newid(),                        -- generic primary key value
        @BusinessRules_UserName,        -- the application user name
        dateadd(second, 30, getdate())  -- cache expiration date
    from #CustOrderHist
    -- drop the temporary table with the custom result set
    drop table #CustOrderHist
end

Execute the script in SQL Management Studio. This is the output that will be produced if you select data from CustOrderHist_Cache table.

Here is the recap of what the script will do if invoked.

1. If the application requires a row count  and the customer ID filter is known, then the script will continue only if there are no unexpired data rows matched to the current user name and customer ID filter . The steps 2-6 will not execute if this condition is not met.
2. The script will delete all cached records matched to the current user name and customer ID filter.
3. The script will create  a temporary table #CustOrderHist to capture the output of the stored procedure CustOrderHist.
4. The script will execute the stored procedure CustOrderHist with the parameter specified in @CustomerID_FilterValue and insert the output to the temporary table #CustOrderHist .
5. The script will copy data from the temporary table to the persistent output cache table CustOrderHist_Cache and enrich the output with parameter value, user name, and expiration date.
6. The temporary table #CustOrderHist is dropped.

If you execute the script a few times in SQL Management Studio then you will notice the status messages indicating that records are being deleted and recreated. The messages will include information about effected rows only every 30 seconds.

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.

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.