Get Recordset from Cache :: Article - 2eNetWorX

Improving Database Performance by Caching serialized Recordsets

by Hakan Eskici

Public Function GetRecordset(SQL)

Dim conn, rsTemp, sBuffer

'Create a temporary recordset
Set rsTemp = Server.CreateObject("AdoDB.Recordset")

'Check whether the requested SQL is in cache
If dDataCache.Exists(SQL) then
	'Already in cache
	
	'Get stream ready
	stCache.Open
	
	'Retrieve data from the cache
	sBuffer = dDataCache.Item(SQL)
	
	'Fill the stream
	stCache.Write sBuffer
	stCache.Position = 0
	
	'Deserialize stream into Recorset
	rsTemp.Open stCache
	
	'Now rsTemp is ready to use in full power
	
	stCache.Close
	
Else
	'Not in cache
	
	'Get the data from the database
	Set connTemp = Server.CreateObject("ADODB.Connection")
	connTemp.Open ConnectionString
	rsTemp.CursorLocation = 2 'adUseServer
	rsTemp.Open SQL, connTemp, 3, 1, 1
	'3, 1, 1 = adOpenStatic, adLockReadOnly, adCmdText
	
	'Save recordset in the stream in ADTG format
	rsTemp.Save stCache, 0 'adPersistADTG
	
	'Optionally, XML serialization can be used:
	'rsTemp.Save stTemp, 1 'adPersistXML
	'However, this would require some modifications

	'Serialize the stream as text
	sBuffer = stCache.Read(-1) 'adReadAll
	
	'Lock the application object for synch issues
	Application.Lock

	'Add the serialized data to the cache
	'We use SQL statement as the unique item key
	dDataCache.Add SQL, sBuffer

	Application.Unlock
	stCache.Close

End If

'Return the recordset, it doesn't matter
'whether it is from cache or from database
Set GetRecordset = rsTemp

'Dispose the temporary object
Set rsTemp = nothing

End Function

Ok, the most important part is done. Although it may look a little bit advanced and complex, basically it performs a simple task of returning a Recordset object either from the database or from the cache.

< Previous Part: Cache Storage and Implementation
> Next Part: Cache Expiration and Removal