Overview
In this step by step tutorial I will guide you to build the fastest method to display records from the database using plain ASP. The technique we are going to use is based on a simple fact that you don't have to hit database every time you want to display records. Take for example a list box which displays names of countries from the database. How many times you are going to change the countries in the database? I bet not often. So if you have to show those countries from the database then it will be better to cache these records in the memory and display them from memory every time you want to display that country list to the user.

Problem
But there are also records which you change daily, hourly or even more often then that so once you cache these records in memory and display them from there then what happens when you change the records in the database? The end-user still continues to see the old cached records.

When I was thinking of writing an article on caching data in memory above problem struck my mind. I wanted to give Stardeveloper users a better method to control the data cached in memory. Then I came up with the following solution.

Solution
Create a method which caches data in memory for a given number of seconds e.g. 300 seconds ( 5 minutes ). During this period the data is displayed from cache in memory, thus extremely fast. Once this time period expires, the data in cache is refreshed with fresh content from the database ( or whatever data source ).

Pretty nice solution. To describe how you can use this technique on your site, take the example of a site which displays latest articles to the users on it's main page from the database. Now if new articles are added between 1 - 600 seconds ( 10 minutes ) time period, wouldn't it be really useful if we can program a cache technique which caches these latest article records in the memory and displays them from there? Then after every given number of seconds ( let's say 300 seconds ) it refreshes the cache and displays new articles to the user. This will result in extremely fast response time to the user.

Demo
Instead of giving you a demo link on the last page, I wanted to show you a real example of this technique so that you understand what we are going to build on the next couple of pages.

The image below shows how the page looks like when it is accessed first time. This page lists first and last names of 17 cricket players from Pakistan cricket team. It retrieves this list from a table in the database and then caches it in memory and displays it to the user. The cache expiry time is 60 seconds. During those 60 seconds it displays this 17 player list from the memory cache, after that it refreshes the cache and loads a fresh list of players from the database.

The top left line tells you if the content has been displayed from in-memory cache or the database. If records are displayed from cache, a green string "Displaying from cache ...(n secs remaining)" is shown, where n is the number of seconds remaining before the content is refreshed from the database. If cache time limit has expired then the records are displayed from the database, a red string "Refreshing Cache ..." is shown on top left.

At the bottom time elapsed between showing the player list from memory / cache is shown in milliseconds. Notice that when content is displayed from memory it takes 0 ms and when content is refreshed from database, it takes on average 30 ms. This enforces my earlier point that content shown from memory is extremely fast.

After the page was refreshed, it displayed the list from the cache, see the status message at top left changing, also keep an eye on the time elapsed pointer at the bottom.

Learning the technique
Now if all this seems interesting to you then move over to the next page where we study the structure of demo application you saw above. Once you read this tutorial you will be able to build really fast database-cache applications for your site.

CacheDemo.mdb Access Database
We need to build a function which accesses the player list from the database. Create a new Microsoft Access database and save it as 'CacheDemo.mdb'. Create a new table 'Names' in it with following structure :

It contains three fields; ID, first_name, last_name. ID is of type Autonumber and is the primary key in this table. first_name and last_name fields are of Text type. Populate this table with some values.

Cache.asp
Now create a new ASP page and save it as Cache.asp. We code a DisplayRecords() function which access CacheDemo.mdb database and retrieves records and builds a complete HTML table containing first and last names of all the players. Copy following code in it :

<%
Function DisplayRecords()
    Const adCmdText = &H0001
    Dim sql, con, rs

    sql = "SELECT id, first_name, last_name FROM Names"

    Set con = Server.CreateObject("ADODB.Connection")
    con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & _
          Server.MapPath("CacheDemo.mdb")

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, con, 2, 4, adCmdText

    If Not rs.EOF Then
      Dim temp
      temp = "<table width=""90%"" align=""center"""
      temp = temp & " border=""1""  bordercolor=""silver"""
      temp = temp & " cellspacing=""2"" cellpadding=""0"">"
      temp = temp & "<tr bgcolor=""#CCDDEE""><td width=""5%"""
      temp = temp & ">ID</td><td>First Name</td>"
      temp = temp & "<td>Last Name</td></tr>"

    While Not rs.EOF
      temp = temp & "<tr><td bgcolor=""#CCDDEE"">"
      temp = temp & rs("ID") & "</td><td>" & rs("first_name")
      temp = temp & "</td><td>" & rs("last_name")
      temp = temp & "</td></tr>"
      rs.MoveNext
    Wend

      temp = temp & "</table>"

      DisplayRecords = temp
    Else
      DisplayRecords = "Data Not Available."
    End If

    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Function
%>

Explanation
Above DisplayRecords() code is extremely simple. It connects to the database then loops through the records to build an HTML table containing all the records. We can display these records directly to the user by using the following code :

<%
Response.Write DisplayRecords()
%>

But every time we call DisplayRecords() we are going to hit the database, thus the database access will be expensive. To use DisplayRecords() method in our cache example we will have to build just one more method; DisplayCachedRecords(). DisplayCachedRecords() is going to be the method which will cache the data from DisplayRecords() into memory and refresh it after every given number of seconds.

DisplayCachedRecords()
In the same Cache.asp page, copy and paste the following DisplayCachedRecords() code above the DisplayRecords() function :