Caching Serialized Recordsets :: Article - 2eNetWorX

Improving Database Performance by Caching serialized Recordsets

by Hakan Eskici

Abstract

This article explains how to build a Recordset caching system for your ASP web site to greatly improve the performance. Directly storing a Recordset object in an application variable is not recommended and should be avoided, because of its thread blocking issues. Therefore we will serialize the Recordset into text, then rebuild the object by deserializing it. By doing so, we can still enjoy the full Recordset object features while avoiding storing it directly in the IIS context.

Introduction

If your web site features some kind of database access, then probably your ASP pages spend most of their time during retrieving information from database tables.

By simply caching the retrieved data for even a short amount of time, you will most likely to observe great performance gain. (At least 10 times faster on most occasions).

There are already known methods to cache page output in the application variables, but what we are after is to cache whole Recordset objects. So we can enjoy the features of the recordset, such as sorting, filtering and so on. (This would be a read-only recordset though, since it's disconnected).

The first step is to have an object that will manage our cache. Although Asp.Net provides a handy Cache object, classic Asp lacks such a feature. So why don't we build our own cache engine then?

Key Features

We have the following key features in mind;

• Easy to use, yet powerful.
• Provides application level cache.
• Enables caching whole Recordset objects, yes - complete Recordsets that we retrieve live from the database once, then reuse whenever needed. What a performace gain we will have!
• Provides methods to expire cache contents manually (Automatic expiration would require a lot of additional code, such as specifying time to live (or expiration date) - similar to the counterpart object in Asp.Net).

Design

Once we have decided the key features we would like to have, now it is time to convert these requirements into a more formal notation.

We will build a Class that will have the following methods and properties as its interface:

Class DataCache
	'Properties
	'Connection string to connect to the database
	Public ConnectionString As String
	
	'Functions (Methods with return values)
	'Returns a recordset object for the SQL statement
	'We use the SQL statement as the unique key
	Public Function GetRecordset(SQL As String)
	
	'Methods
	'Removes the cache entry for the SQL string
	Public Sub Remove(SQL As String)
	
	'Removes all cache contents
	Public Sub RemoveAll()
	
	'Expires (removes) cache entries that belong
	'to the given Table
	Public Sub ExpireTable(TableName As String)
	
End Class

'Declare cache and recordset objects
Dim MyCache, rs

'Create an instance of the DataCache
Set MyCache = New DataCache

'Specify the connection string
MyCache.ConnectionString = "(some connection string)"

'Get a recordset from the cache
Set rs = MyCache.GetRecordset("SELECT * FROM Customers")

'At this point, we expect to have a Recordset (rs) filled
'with records from the Customers table. The data would
'come directly from the database if this is the first
'time we get this recordset. If this is not the first
'access, then it would come from our cache, very fast!