I created the following file below to cache and return information from an Access database. I would appreciate help optimizing this because:<BR><BR>1. It could be useful for others.<BR>2. I place it in the public domain.<BR>3. It&#039;s an interesting problem.<BR><BR>So let me know:<BR><BR>1. Is this a good approach?<BR>2. Are there ways to make this more efficient?<BR>3. Can anyone do a little more testing on it?<BR><BR>------------------------------------------------<BR><BR>Functions for database access used as follows:<BR><BR>arrRS = GetTable(strSQL)<BR>--This function returns a GetRows 2d array with some additional information, either from the recordset directly or from a cache in the application variable<BR><BR>theFieldValue = GetValue(arrRS,strFieldName,intRecordNum)<BR>--Returns the value in the array arrRS at the column strFieldName and the row intRecordNum (zero-based).<BR><BR>intLastRow = LastRow(arrRS)<BR>--Returns the highest number possible to use as intRecordNum in the previous function<BR><BR>------------------------------------------------<BR><BR>Customizable variables are as follows:<BR><BR>cCacheTime<BR>--Represents the number of minutes that pass before cached data is consedered old.<BR><BR>cMinPerSQL<BR>--Number of times a SQL statement must be called before it is cached. This is to prevent searches and occasional use from caching items<BR><BR>cMaxRecordsets<BR>--Number of recordsets to store. The prune function will trim excess records<BR><BR>cPruneRate = .01<BR>--Prune the cache this fraction of the time. If rnd&#060;cPruneRate then the sub is triggered which eliminates old data and excess recordsets.<BR><BR>------------------------------------------------<BR><BR>The Code:<BR><BR>&#060;%@ EnableSessionState = False %&#062;<BR>&#060;% <BR>Response.Buffer = True<BR><BR>const cCacheTime = 20<BR>&#039;minutes, then refresh data<BR>const cMinPerSQL = 3<BR>&#039;Number of times a SQL statement must be called before it is cached<BR>const cMaxRecordsets = 100<BR>&#039;Number of recordsets to store<BR>const cPruneRate = .01<BR>&#039;Prune the cache this fraction of the time.<BR>const sDbSource = "../database.mdb"<BR>&#039;Access Database Location<BR><BR><BR>&#039;Initialization<BR>Dim myConn<BR>if request.querystring("prune") = "yes" then Prune<BR>randomize timer<BR>if rnd &#060; cPruneRate then openPruner<BR><BR>Function GetTable (sSQL)<BR> vAppResults = Application(sSQL)<BR> If isarray(vAppResults) Then <BR> If DateDiff("n", vAppResults(0, ubound(vAppResults,2)), now) &#060; cCacheTime Then<BR> GetTable = vAppResults<BR> Exit Function<BR> Else <BR> nTimesCalled = cMinPerSQL<BR> End If<BR> Elseif isempty(vAppResults) Then <BR> Application.Lock<BR> Application(sSQL) = now & "##1"<BR> Application.Unlock <BR> nTimesCalled = 1<BR> Elseif DateDiff("n", split(vAppResults,"##")(0), now) &#060; cCacheTime Then<BR> nTimesCalled = split(vAppResults,"##")(1) + 1<BR> Application.Lock<BR> Application(sSQL) = now & "##" & nTimesCalled<BR> Application.Unlock<BR> Else <BR> Application.Lock<BR> Application(sSQL) = now & "##1"<BR> Application.Unlock <BR> nTimesCalled = 1<BR> End If<BR> If NOT isObject(myConn) Then<BR> If ubound(split(sDbSource,":")) = 0 Then <BR> sDbSourceTrans = Server.MapPath(sDbSource)<BR> Else<BR> sDbSourceTrans = sDbSource<BR> End If<BR> sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDbSourceTrans<BR> Set myConn = Server.CreateObject("ADODB.Connection")<BR> myConn.Open sConn<BR> End If<BR> set rsCache = myConn.Execute(sSQL)<BR> If rsCache.eof Then<BR> getTable = ""<BR> Exit Function<BR> End If<BR> aTable = rsCache.getRows<BR> nUboundDim2 = ubound(aTable,2)<BR> ReDim Preserve aTable(ubound(aTable,1), nUboundDim2 + 2)<BR> nFields = rsCache.Fields.Count<BR> For iIncrement = 0 To nFields - 1<BR> aTable(iIncrement, nUboundDim2 + 1) = lcase(rsCache(iIncrement).Name)<BR> Next<BR> rsCache.close<BR> aTable(0, nUboundDim2 + 2) = now<BR> GetTable = aTable<BR> If nTimesCalled &#062;= cMinPerSQL Then<BR> Application.Lock<BR> Application(sSQL) = aTable<BR> Application.Unlock<BR> End If<BR>End Function<BR><BR>Function LastRow(aArray)<BR> if isarray(aArray) then<BR> LastRow = ubound(aArray, 2) - 2<BR> else<BR> LastRow = -1<BR> end if<BR>End Function<BR><BR>Function GetValue(aArray, sColName, nRow)<BR> colUbound = ubound(aArray,1)<BR> rowUbound = ubound(aArray,2)<BR> sColName = lcase(sColName)<BR> If nRow &#060; 0 OR nRow &#062; rowUbound - 2 Then Exit Function<BR> For iIncrement = 1 to colUbound<BR> If sColname = aArray(iIncrement, rowUbound - 1) Then<BR> GetValue = aArray(iIncrement, nRow)<BR> Exit Function<BR> End If<BR> Next<BR>End Function<BR><BR>Sub Prune ()<BR> t = timer<BR> Response.write "&#060;script language=""Javascript""&#062;" & vbnewline & "window.close()" & vbnewline & "&#060;/script&#062;"<BR> Response.flush<BR> Dim aTemp(10)<BR> Dim iInc<BR> Dim nRecordsets<BR> For Each item In Application.Contents<BR> sTemp = Application(item)<BR> If isarray(sTemp) Then <BR> nRecordsets = nRecordsets + 1<BR> If DateDiff("n", sTemp(0, ubound(sTemp,2)), now) &#062; cCacheTime Then<BR> iInc = iInc + 1<BR> aTemp(iInc) = item<BR> Elseif nRecordsets &#062; cMaxRecordsets AND rnd &#060; (5 / cMaxRecordsets) Then <BR> aTemp(iInc) = item<BR> End If <BR> Elseif DateDiff("n", split(sTemp,"##")(0), now) &#062; cCacheTime Then<BR> iInc = iInc + 1<BR> aTemp(iInc) = item<BR> End If<BR> if iInc = 10 then<BR> eraseArray aTemp<BR> Redim aTemp(10)<BR> End If<BR> Next<BR> eraseArray aTemp<BR> Response.end<BR>End Sub<BR><BR>Sub eraseArray (inputArray)<BR> Application.Lock<BR> For each item2 in inputArray<BR> if not isempty(item2) then Application.Contents.Remove(item2)<BR> Next<BR> Application.Unlock<BR>End Sub <BR><BR>Sub openPruner ()<BR> response.write ("&#060;script language=""Javascript""&#062;" & vbnewline & "f=window.open(&#039;" & request.servervariables("URL") & "?prune=yes&#039;,&#039;&#039;,&#039;width=100,hei ght=100,top=4000,left=4000&#039;);f.blur()" & vbnewline & "&#060;/script&#062;")<BR>End Sub<BR>%&#062;