Output Caching Script

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.