Paging and caching large record sets Support

Today we look at viewing large record sets, and how to optimise this process both for the person doing the viewing and for the server processing the page. To handle the former, we'll use a technique known as paging, and for the latter, caching. This caching is not the client side browser caching we've all come to know and hate; this caching is done entirely on the server.

 

The code supplied is half the length of the Dreamweaver-generated code, and is hugely beneficial if you have a site where users look through large lists of data without amending it.

Advertisement DMXzone Paginator ASP

Add the coolest page navigation to your site and make browsing through large lists of items or tables easy and quick. Choose from 22 different styles, used by many sites such as Digg, Yahoo, Flickr and much more, to fit perfectly with your design and display the total number of items.

All paging styles are fully CSS based, so you can always fine tune the colors and borders to your site design.

 

However…

There is a catch. Dreamweaver inserted over 200 lines of code for this paging capability! What if this paging technique is to be used on a public website, where usage could conceivably grow to thousands of users at any given time? This code, however useful, is not optimal. Luckily, a lot can be done to fix this!

Apart from cleaning up this code, we'll also look at how to optimise this process from a logical standpoint (as opposed to a technical one). If this page is to be used often, it is quite conceivable that the very same recordset will be displayed to multiple users at a given time. If not, users who page back and forth between subsets of their recordset (using the above paging code) still retrieve a fresh instance of that record set every time they hit 'Next' or 'Previous'. Why not use one instance for the paging session instead? This will save precious database connectivity resources.

To make this work, we'd have to make sure that a couple things are true. Firstly, this would only really provide performance benefit if insertions and updates to this data are few, and views is high. Why? Well, every time that the data changes, the cached instance of the recordset in memory is no longer consistent with the data in the database. This means that we'd have to regenerate this cached copy if the data changes. Couple that fact with a high change rate and you start to come back to the recordset being generated on almost every page view.

Even so, if the change rate is moderate compared to the number of page views, there is still a performance benefit. Let's look at how to implement this caching technique for a single user first.

Session based recordset caching

Unfortunately, to be able to cache data in the Session store (the area of memory dedicated to the user's session), we need to transform the data in the recordset into intrinsic JavaScript objects, as we cannot

store the recordset (with all its data) itself in the Session store. Although there is a very slight performance hit in doing this, implementing the caching which depends on doing so provides massive performance benefit.

    FYI: JavaScript objects are basically named containers for variables. Objects can contain more objects, which can contain more objects, each with their own variables, arrays, and so on. They basically provide a way to structure information.

There is a useful side benefit too; the data becomes prepared for viewing only once. The catch: we will use almost none of Dreamweaver's generated code. The only code we do use is the bit that creates the recordset. The rest is hand-typed. As a consequence, you'll basically start with a new ASP JavaScript page. Simply copy the code below into your document.

Here's the solution. There's a fair bit to go through here. If it looks daunting, skip ahead to the walk-through! I urge you to read through it anyway, as this will make the walk-through an easier process for you.

    <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    <!--#include file="../Connections/PagingSample.asp" -->
    <%
    function FormatShortDate ( dateObject ) {
     return dateObject.getDate() + "&middot;" +
     ( dateObject.getMonth() ) +
     "&middot;" + dateObject.getFullYear();
    }
    function FormatCurrency ( value ) {
     return "R" + value + ".00";
    }
    var orders, order;
    var pageSize = 1, currentPage = 0, paging, nextPage, totalPages;
    var orderIndex = 0, endPoint, recordCount;
    if ( Session( "Orders" ) != null &&
     Application( "ordersChanged" ) != "changed" ) {
     orders = Session( "Orders" );
     if ( String( Request( "page" ) ) != "undefined" ) {
     currentPage = parseInt( Request( "page" ) );
     }
    } else {
     var ordersRS = Server.CreateObject("ADODB.Recordset");
     ordersRS.ActiveConnection = MM_PagingSample_STRING;
     ordersRS.Source = "SELECT OrderDate, Client, Value, Products FROM Orders ORDER BY OrderDate ASC";
     ordersRS.CursorType = 0;
     ordersRS.CursorLocation = 2;
     ordersRS.LockType = 1;
     ordersRS.Open();
     orders = new Array();
     while ( !ordersRS.EOF ) {
     order = new Object();
     order.OrderDate = FormatShortDate( new Date( ordersRS( "OrderDate" ) ) );
     order.Client = String( ordersRS( "Client" ) );
     order.Value = FormatCurrency( parseFloat( ordersRS( "Value" ) ) );
     order.Products = parseInt( ordersRS( "Products" ) );
     orders.push( order );
     ordersRS.moveNext;
     }
     ordersRS.Close();
     Session( "Orders" ) = orders;
    }
    recordCount = orders.length;
    paging = recordCount > pageSize;
    if ( paging ) {
     nextPage = currentPage + 1;
     totalPages = Math.ceil( recordCount / pageSize );
     orderIndex = currentPage * pageSize;
     endPoint = orderIndex + pageSize;
     if ( endPoint > recordCount ) {
     endPoint = recordCount;
     }
    } else {
     endPoint = recordCount;
    }
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
     <title>Untitled Document</title>
     <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    </head>
    <body>
     <table width="400" border="0">
    <%
    if ( paging ) {
    %>
     <tr>
     <td colspan="4" align="center">
    <%
     if ( currentPage > 0 ) {
    %>
     <a href=" PagingSample.asp?page=<%= currentPage - 1 %>">Previous</a>
    <%
     }
     if ( nextPage < totalPages ) {
    %>
     <a href=" PagingSample.asp?page=<%= nextPage %>">Next</a>
    <%
     }
    %>
     </td>
     </tr>
    <%
    }
    %>
     <tr>
     <th align="left">Order Date</th>
     <th align="left">Client</th>
     <th align="center">Value</th>
     <th align="center">Products</th>
     </tr>
    <%
    orderIndex--;
    while ( ++orderIndex < endPoint ) {
     order = orders[ orderIndex ];
    %>
     <tr>
     <td><%= order.OrderDate %></td>
     <td><%= order.Client %></td>
     <td align="right"><%= order.Value %></td>
     <td align="right"><%= order.Products %></td>
     </tr>
    <%
    }
    %>
     </table>
    </body>
    </html>

Okay! Let's go through it step by step.