Data caching in Classic ASP using an object-oriented approach

One effective way to improve data access speed is caching frequently used data, such as country lists or top articles. When data doesn’t change often, perhaps once a day or every few hours, it’s inefficient to query the database for each page request.

While caching HTML output of page fragments (Fragment Caching) or entire pages (Output Caching) is often suggested, Data Caching can be more efficient. For instance, you might need to display the same country list on multiple pages or sections with different appearances or use it programmatically.

This post expands on a previous one about Data Access Layer (DAL) Classes for encapsulating data access code for specific tables or objects. We’ll modify the DAL to incorporate data caching. For reference, see the previous post: Data Access Layer for Classic ASP.

The “Users” class now handles saving and reading from the cache. Data is saved in either ADO XML or Advanced Data Tablegram (ADTG) binary format using the ADO Recordset’s “Save” method. This approach has several benefits: minimal changes to existing data access code for reading from the cache, similar behavior between opening a recordset from XML and the database when using paging, and the ability to filter records using the ADO Recordset’s “Filter” method, just like SQL queries.

Here is the updated class:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
Class Users
'private Arr to hold users Objects
Private arr

'0 : Binary ADTG , 1: XML
Public CacheType 'as Byte
' Cache File Path
Public CachePath 'as String

''''' Paging Variables
Private mPageCount 'as Long
Public PageIndex 'as Long
Public PageSize 'as Long

'for getting top records if larget than 0
Public TopRecords 'as Long

'destroy arr
Private Sub Class_Terminate()
  Erase arr
end Sub

Private Sub Class_Initialize()
  Initialize
  TopRecords = 0
  PageIndex = 0
  PageSize = 0

  CacheType = 0
  CachePath = ""
end Sub

'Initialize collection
Public Sub Initialize()
  redim Arr(-1)
  mPageCount = 0
End Sub

'return Upper Bound of objects array
Public Property get UpperBound()
  UpperBound = UBound(arr)
End Property

'return object at index i
Public Property Get ElementAt(i)
  set ElementAt = arr(i)
End Property

'return Upper Bound of objects array
Public Property Get PageCount()
  PageCount = mPageCount
End Property

'Select users by Status ( u can add more search parameters or create another selectUsers method)
Public Sub SelectUsers(Status)
  Initialize

  Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
  'Do paging
  If PageSize>0 And PageIndex>0 Then
    RS.CursorLocation = adUseClient
    RS.CacheSize = PageSize
    RS.PageSize = PageSize
  End If

  'if open from XML
  If CachePath>"" Then
    If Status>0 Then rs.Filter = "Status="& Status
    Rs.Open CachePath,,,,adCmdFile

  'if Open from DB
  Else
    If TopRecords>0 Then
      rs.MaxRecords = TopRecords
      Top = " top "& TopRecords &" "
    End If
    Dim SQL : SQL= "SELECT "& Top &" * From users"
    If Status>0 Then SQL = SQL & " where Status="& Status

    rs.Open SQL , ConnStr, adOpenForwardOnly,adLockReadOnly,adCmdText
  End If

  ' if paging : get page count
  If Not rs.EOF And PageSize>0 And PageIndex>0 Then
    RS.AbsolutePage = PageIndex
    mPageCount = RS.PageCount
  End If

  Dim i : i=0
  '(TopRecords=0 Or i is needed to get correct Top N records when opening from Cache
  ' ,MaxRecords doesn't seem to work in that case
  While Not rs.EOF And (TopRecords=0 Or i<TopRecords) And (PageSize=0 Or i<PageSize)
    'loop until EOF or Paging Size reached
    'create Object and set Values then add to collection
    Dim u : Set u = New User
    u.ID = CLng(Rs("ID"))
    u.Name = Rs("Name")
    u.Email = Rs("Email")
    u.Password = Rs("Password")
    u.LastLogin = cdate(rs("LastLogin"))
    u.Status = cbyte(Rs("Status"))

    ReDim Preserve arr(i)
    Set arr(i) = u
    set u = Nothing

    rs.MoveNext
    i=i+1
  Wend
  rs.Close
  Set rs = Nothing
End Sub

' Open Recordset and Save it
Public Sub CacheUsers(Status)
  If CachePath="" Then Exit Sub

  Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
  Dim Top
  If TopRecords>0 Then
    rs.MaxRecords = TopRecords
    Top = " top "& TopRecords &" "
  end if
  Dim SQL : SQL= "SELECT "& Top &" * From users"
  If Status>0 Then SQL = SQL & " where Status="& Status

  rs.Open SQL , ConnStr, adOpenForwardOnly,adLockReadOnly,adCmdText
  Call SaveRS(rs)
  rs.Close
  Set rs = Nothing
End Sub

'Handle saving Recordset to Stream
Private Sub SaveRS(rs)
  Const adTypeText = 2
  Const adTypeBinary = 1
  Const adSaveCreateOverWrite = 2
  Const adPersistXML = 1
  Const adPersistADTG = 0

  Dim Stream : Set Stream = Server.CreateObject("ADODB.Stream")
  If CacheType=1 Or CacheType=2 Then
    Stream.Type = adTypeText
    Stream.Open
    rs.Save Stream, adPersistXML
  Else
    Stream.Type = adTypeBinary
    Stream.Open
    rs.Save Stream, adPersistADTG
  end If
  Application.Lock
    Stream.SaveToFile CachePath,adSaveCreateOverWrite
    Stream.Close
  Application.UnLock
  Set Stream = Nothing
End Sub

End Class

Sample Usage:

1- Saving the Cache after Target Data Updates:

1
2
3
4
5
6
Dim aUsers : Set aUsers = New Users
aUsers.CacheType = 1 'XML cache
aUsers.CachePath = Server.MapPath("users.xml")
aUsers.TopRecords = Top
Call aUsers.CacheUsers(0)
Set aUsers = Nothing

2- Reading from the Cache:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Dim aUsers : Set aUsers = New Users
aUsers.CacheType = 1 'XML cache
aUsers.CachePath = Server.MapPath("users.xml")
aUsers.TopRecords = Top
Call aUsers.SelectUsers(0)
Dim i,aUser
For i=0 To aUsers.UpperBound
  Set aUser = aUsers.ElementAt(i)
  'do something
  Set aUser = Nothing
Next
Set aUsers = Nothing

Timing Comparison:

A comparison test was done between retrieving data from a SQL Server and XML/Binary files. However, reading from a local SQL Server is always quicker. To simulate a real-world scenario, a LAN/WAN connection was emulated for the SQL Server: Simulating WAN Connection for SQL Server Performance Testing.

To simulate concurrent IIS connections and measure timing, the Microsoft Web Stress Tool was used on a PC with these specs: P3.2GHZ CPU, 1GB RAM, Windows XP, and SQL Server 2005 Express.

Method / Records	100	500	1000	3000
Open WAN SQL Server	559.25	614.80	980.85	2489.85
Open LAN SQL Server	276.70	436.00	777.60	2368.90
Open XML Cache	72.25	369.95	821.10	2469.31
Open Binary Cache	60.45	311.25	666.45	2062.20

The results indicate that retrieving less than 1000 records from the XML cache is marginally faster than accessing a LAN SQL Server. The time savings become more significant when compared to a WAN SQL Server. However, for more than 1000 records, XML performance deteriorates due to increased loading and parsing overhead.

Furthermore, reading from binary (ADTG) consistently outperforms both XML and LAN/WAN SQL Server data retrieval. Additionally, binary files are roughly 50% smaller compared to their XML counterparts.