Cache Engine :: Article - 2eNetWorX

Improving Database Performance by Caching serialized Recordsets

by Hakan Eskici

Well, we have a fully working cache engine implementation, but how do we use it? There are two things in your task list:

1. Converting existing database queries to cached versions
2. Adding manual expiration triggers

Step #1 should not be difficult, suppose that you have such a code block:

<%

Dim conn, rs
Dim SQL, sConn

Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
	
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Persist Security Info=False;" & _
	"Data Source=C:\Data\Customers.mdb"

conn.Open sConn
Set rs.ActiveConnection = conn
rs.CursorType = 3 'adOpenStatic

SQL = "SELECT SUM(Balance) AS Total FROM Customers"
	
rs.Open SQL,,,2 'adCmdTable

Response.Write "Total = " & rs("Total")

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

%>

If there are thousands of records in the Customers table, performing a SUM would take up to a few seconds each time you visit the page with that code.

Assuming your cache class is saved as cache.asp, you simply replace above code block with the one below:

<!--#include file="cache.asp"-->
<%

'Declare cache and recordset objects
Dim MyCache, rs
Dim SQL, sConn

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

'Specify the connection string
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Persist Security Info=False;" & _
	"Data Source=C:\Data\Customers.mdb"
	
MyCache.ConnectionString = sConn

'Get a recordset from the cache
SQL = "SELECT SUM(Balance) AS Total FROM Customers"

Set rs = MyCache.GetRecordset(SQL)

Response.Write "Total = " & rs("Total")

rs.Close
Set rs = Nothing
Set MyCache = Nothing

%>

The GetRecordset() function will only take long time when it's first called. (Of course, not any longer than the classic connection / recordset method). Later calls will immediately return your recordset in a few miliseconds. That's hundreds of times faster!

< Previous Part: Cache Expiration and Removal
> Next Part: Case Study and Final Words