A very important method of increasing data access efficiency is to cache frequently accessed data. like country list or top articles.. in situations where the data changes less frequently, such as once a day or every few hours, it is inefficient to hit the database for every page request.

As many would suggest to cache the HTML Output of a portion of a page(Fragment Caching) as a country select box or caching the entire page(Output Caching), there are cases where Data Caching will be more powerful. for example you may need to display that list of countries in more than one page/Portion with different look and/or use that list programmatically.

In a previous post [Data Access Layer for Classic ASP]: I proposed DAL Classes to encapsulate Data Access code for a certain Table/Object, and now I'll extend the DAL to handle Data caching too, please refer to my previous post before continuing..

I've modified the "Users" class to handle saving and reading from cache, which is saved to ADO XML format or Advanced Data Tablegram (ADTG) Binary format using "Save" method of ADO Recordset, Which has many advantages :
- You don't need to change much of your Data Access code to read from cache
- Opening the recordset from XML will act the same as Opening Database when you use Paging
- You can use The "Filter" method of ADO Recordset to filter records as you would do in a SQL Query

Here is the New Class
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<TopRecords) condition 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- Save Cache when Target Data is updated
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- Read Cache
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

Time Comparison
I did a timing test to compare between reading from a SQL Server and from XML/Binary file, but Reading from a local SQL Server (on the same machine as IIS) will be always faster, so to get real world results i simulated a LAN/WAN Connection on SQL Server [How to? check: Simulating WAN Connection for SQL Server Performance Testing].

And to simulate concurrent connections on IIS and calculate timing i used the very neat [Microsoft Web Stress Tool] on a PC of P3.2GHZ, 1GB Ram, Windows XP, 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

As you can see reading less than 1000 records from XML Cache is slightly faster than reading from LAN SQL Server and time saving gets bigger when compared to WAN SQL server(reading >1000 records gets slower in XML due to loading&parsing overhead).
You can also notice that reading from Binary(ADTG) is faster than reading from XML and LAN/WAN SQL Server in all cases, and files created in binary are Approx. 50% smaller than the equivalent XML files.

I needed to do some testing on SQL Server on my PC, but response was of course very fast, so i needed to simulate WAN connection to get some real world results..

i found a good article [How To: Simulate WAN Connections in Your Own Test Lab, For Free!] by Michel Roth

Since WAN main characteristics are : limited in bandwidth and high in latency, we can use one of two software:

1- NetLimiter Pro (commercial-28-day evaluation period)
it can limit the bandwidth per process. You can use this ability in two ways:
a. You can install it on the Server (ex:SQL Server) and limit the incoming bandwidth.
b. You can install it on the Client machine(ex:IIS) and limit the outgoing bandwidth for the client(Preferred).

2. TMnetSim Network Simulator (free!)
a *free* tool to allow you to simulate network latency & packet loss!,You can install it on the client or on the server. I gonna use the tool on the Server.

Here are the steps to setup that tool for testing SQL Server 2005 Express on your local PC:

1. Download zip file and run executable
2. SQL Server default port is 1433 so:
   Under 'Outbound Connection' >> enter IP: 127.0.0.1, Port : 1433
   Under 'Inbound Connection' >> enter port: 1422 (any other port!)
   Under 'inbound-->outbound policies' >> Delay Type : Gausian, Delay Base:250 , Daley Jitter : 25
3. click "Start"
4. Setup your Connection String on ASP
   Provider=SQLOLEDB; Data Source=127.0.0.1\sqlexpress,1422;Initial Catalog=db;User Id=user;Password=pass;
   or ASP.Net :
   <connectionStrings>
<add name="SQLConn" connectionString="Data Source=127.0.0.1\sqlexpress,1422;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn" providerName="System.Data.SqlClient"/>
</connectionStrings>
   
5. If things running ok, you should see connection information (Latency- Bandwidth- Packet Loss)

When error occurs while you compile or run an ASP page, IIS generates a 500;100 error and executes a Server.Transfer() method to pass control to the currently defined custom error page. (By default this page is "WinDir/help/iishelp/common/500-100.asp")

When control is passed to the custom error page, the Server.GetLastError() method can be used to obtain detailed information regarding the error that occurred. for more info [Creating Custom ASP Error Pages]

While you can setup a custom ASP error page for some website as described in the MSDN article mentioned before; why not customize the default IIS error 500 page [WinDir/help/iishelp/common/500-100.asp] directly,To present users with a friendly error message and do server-wide error logging and/or send it to IT Email..

This is modification for the 500-100.asp to display an error message and send email to IT:
<%@ language="VBScript" %>
<% Option Explicit

If Response.Buffer Then
  Response.Clear
  Response.Status = "500 Internal Server Error"
  Response.ContentType = "text/html"
  Response.Expires = 0
End If

Call SendError()
%>
<html>
<head>
  <style>
  Body,TD  {FONT-SIZE: 11px; FONT-FAMILY: Tahoma }
  </style>
  <META NAME="ROBOTS" CONTENT="NOINDEX">
  <title>Unexpected Error</title>
  <META HTTP-EQUIV="Content-Type" Content="text-html; charset=Windows-1252">
</head>
<body>
  <h3 align="center">Unexpectd Error</h3>
  An unexpected error has occurred. We apologize for the inconvenience.
  <p>Please try the following:</p>
  <ul>
   <li>Click the <a href="javascript:location.reload()">Refresh</a> button, or try again later.</li>
   <li>Open the home page, and then look for links to the information you want. </li>
  </ul>
</body>
</html>
<%
Sub SendError()
  Dim er : Set er = Server.GetLastError      
  Dim msg : msg = "Error 500 on "& Now &"<br>"
  msg = msg & "category:"& er.Category & "<br>"
  If er.ASPCode>"" Then msg = msg & "aspcode:"& er.ASPCode & "<br>"
  msg = msg & "number:"& hex(er.Number) & "<br>"
  
  If er.ASPDescription > "" Then
    msg = msg & "description:"& er.ASPDescription & "<br>"
  ElseIf er.Description >"" Then
    msg = msg & "description:"& er.Description & "<br>"
  end If
  
  If er.Source > "" Then msg = msg & "source:"& er.Source & "<br>"
  If er.File <> "?" Then
    msg = msg & "file:"& er.File & "<br>"
    If er.Line > 0 Then msg = msg & "line:"& er.Line & "<br>"
    If er.Column > 0 Then msg = msg & "column:"& er.Column & "<br>"
  End If
  
  msg = msg & "USER_AGENT:"& Request.ServerVariables("HTTP_USER_AGENT") & "<br>"
  msg = msg & "REQUEST_METHOD:"& Request.ServerVariables("REQUEST_METHOD") & "<br>"
  msg = msg & "SCRIPT_NAME:"& Request.ServerVariables("SCRIPT_NAME") & "<br>"
  if Request.QueryString>""Then msg = msg & "QueryString:"& Request.QueryString & "<br>"
  if Request.Form>""Then msg = msg & "Post:"& Left(Request.Form,500) & "<br>"
  
  Call SendEmail(IT_Email,IT_Email,"Error 500",msg)
End Sub
%>

* don't forget to disable "Show friendly HTTP error messages" In IE under Tools/Internet Options/Advanced

URL rewriting is usually needed to make a URL for a dynamic web page more presentable for the reader or for search engines optimization.

For example, a regular ASP URL might look like this:
http://www.site.com/Articles/Article.asp?id=20

A more presentable way of rewriting the URL might be:
http://www.site.com/Articles/20.htm

of course, you can write HTML/ASP file for each article, but that would be much of I/O overhead on adding/updating articles and for reading too..

one solution is to use an ISAPI filter but many people want to avoid this for due to either the limitation on the skills, a limitation of the hosting service they use, or just to avoid complexity and potential risk. It also makes the solution less portable.

A much simple solution, is to use 404 custom error page in IIS, here are steps and code:
1- Create "URL-Rewrite.asp", under Articles folder.
2- in IIS , right-click on "Articles" folder > properties > Custom errors > Select 404 and click "Edit Properties" ..
set Message Type : "URL", and set URL : "/Articles/URL-Rewrite.asp"
3- Place this code in "URL-Rewrite.asp"
<% option Explicit

Dim Er,ID
Set Er = Server.GetLastError()
If Not Er Is Nothing Then
  '' For 404: URL will be passed as query string
  ID = GetURLID(Request.QueryString )
  'Http error 400 won't raise a code error
  If ID>0 And Er.Number=0 Then
    Response.Status = 200
    Call DisplayArticle(ID)

  ' error 500 or similar  
  ElseIf Er.Number<>0 Then
    Response.Write "Unexpected error was occured"  

  ' undesired URL  
  Else
    Response.Status = 404
    Response.Write "Page cannot be found"  
  End if  
End If  
Set Er = Nothing

Function GetURLID(URL)
  ' Extract ID using regular expressions
  Dim ID : ID = 0
  Dim reg : Set reg = New RegExp
  reg.Global = False
  reg.IgnoreCase = True
  reg.Pattern = "^404;http://www.site.com/Articles/(\d+).htm$"
  if reg.Test(URL) Then
    ID= reg.Replace(URL ,"$1")
  End If
  Set reg = Nothing
  GetURLID = ID
End Function

Sub DisplayArticle(ID)
  '''' here you will place the real code that read article form database and write it
  Response.Write   "<html>" &_
          "<head>" &_
          "<title>Article "& ID &"</title>" &_
          "</head>" &_
          "<body>" &_
          "Content " & ID &_
          "</body>" &_
          "</html>"
End Sub
%>

now,when requesting the url "http://www.site.com/Articles/20.htm", the IIS doesn't find the file so it transfer execution to the custom error page "http://www.site.com/Articles/URL-Rewrite.asp" and sending "404;http://www.site.com/Articles/20.htm" as query string.
The Transfer happens by calling "server.Transfer" which keeps the existing query string and any form variables available to the page it is transferring to, in our case "URL-Rewrite.asp".