Expire Cache :: Article - 2eNetWorX

Improving Database Performance by Caching serialized Recordsets

by Hakan Eskici

Public Sub Remove(SQL)

	'Lock the application
	Application.Lock
	
	'Check whether the data is in cache
	If dDataCache.Exists(SQL) Then 
		'It's in cache, so remove it
		dDataCache.Remove SQL
	End If
	
	'Unlock the application
	Application.UnLock
	
End Sub

The trick here is to know when to expire (remove) the cached entries. The best approach would be calling the Remove() method when we are sure that the underlying database table has changed. If that table gets updated very often (such as in a few minutes) it might be useful to delay calling Remove() for a couple of updates.

The bad thing about the Remove() method is that we need to know the exact SQL statement of the cached data. In most cases, this is an annoying task. Wouldn't it be nice to have a method that will expire all entries that belong to a specific database table?

So we could call it like:

MyCache.ExpireTable "Customers"

Then it would expire all entries such as:

SELECT * FROM Customers
SELECT FirstName FROM Customers WHERE CustomerID = 531
SELECT SUM(Balance) FROM Customers
SELECT TOP 5 FROM Customers ORDER BY Balance DESC
... etc

There we go:

Public Sub ExpireTable(TableName)

'Get keys as an array
aKeys = dDataCache.Keys

'How many entries?
iCount = dDataCache.Count

'Enumerate cache
For i = 0 To iCount - 1

	'Get SQL
	sSQL = aKeys(i)
	
	sItemTable = ""
	
	'Find the underlying table
	aSQL = Split(sSQL, " ")
	For iSQL = 0 To UBound(aSQL) - 1
		'Look for FROM keyword
		If LCase(aSQL(iSQL)) = "from" Then
			'Found it
			sItemTable = aSQL(iSQL + 1)
			Exit For
		End if
	Next
	
	If LCase(TableName) = LCase(sItemTable) Then
		'Remove the entry
		dDataCache.Remove sSQL
	End If

Next
	
End Sub

A little bit tricky, but that's all we can do for now.

Finally, we will build a method that removes all entries from the cache.

That one is fairly easy, so there are no code comments:

Public Sub RemoveAll

	Application.Lock
	dDataCache.RemoveAll
	Application.Unlock

End Sub