Access 2000 数据库 80 万记录通用快速分页类[转帖]

主要思路: 用一条语句统计(Count)出记录数(而不在查询时获得 RecordCount 属性), 缓存在 Cookies 中, 跳转时就不用再次统计. 使用 ADO 的 AbsolutePage 属性进行页面跳转即可. 为方便调用而写成类, 代码主要地方已有说明

硬件环境: AMD Athlon XP 2600+, 256 DDR
软件环境: MS Windows 2000 Advanced Server + IIS 5.0 + Access 2000 + IE 6.0
测试结果: 初次运行在 250(首页) - 400(末页)毫秒, (记录数缓存后)在页面间跳转稳定在 47 毫秒以下.第1页跳到最后一页不多于 350 毫秒

适用范围: 用于普通分页. 不适用于有较复杂的查询时: 如条件为"[Title] Like '%最爱%'", 查询的时间大大增加, 就算 Title 字段作了索引也没用. :(

<%@LANGUAGE = "VBScript" CODEPAGE="936"%><%Option Explicit%><% Dim intDateStart intDateStart = Timer() Rem ## 打开数据库连接 Rem #################################################################  function f__OpenConn()   Dim strDbPath   Dim connstr   strDbPath = "../db/test.mdb"   connstr  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   connstr  = connstr & Server.MapPath(strDbPath)   Set conn  = Server.CreateObject("Adodb.Connection")   conn.open connstr  End function Rem #################################################################  Rem ## 关闭数据库连接 Rem #################################################################  function f__CloseConn()   If IsObject(conn) Then    conn.close   End If   Set conn = nothing  End function Rem ################################################################# Rem 获得执行时间 Rem ################################################################# function getTimeOver(iflag)  Dim tTimeOver  If iflag = 1 Then   tTimeOver = FormatNumber(Timer() - intDateStart, 6, true)   getTimeOver = " 执行时间: " & tTimeOver & " 秒"  Else   tTimeOver = FormatNumber((Timer() - intDateStart) * 1000, 3, true)   getTimeOver = " 执行时间: " & tTimeOver & " 毫秒"  End If End function Rem ################################################################# Class Cls_PageView  Private sbooInitState  Private sstrCookiesName  Private sstrPageUrl  Private sstrPageVar  Private sstrTableName  Private sstrFieldsList  Private sstrCondiction  Private sstrOrderList  Private sstrPrimaryKey  Private sintRefresh    Private sintRecordCount  Private sintPageSize  Private sintPageNow  Private sintPageMax    Private sobjConn    Private sstrPageInfo    Private Sub Class_Initialize   Call ClearVars()  End Sub     Private Sub class_terminate()   Set sobjConn = nothing  End Sub    Public Sub ClearVars()   sbooInitState = False   sstrCookiesName = ""   sstrPageUrl = ""   sstrPageVar = "page"   sstrTableName = ""   sstrFieldsList = ""   sstrCondiction = ""   sstrOrderList = ""   sstrPrimaryKey = ""   sintRefresh = 0      sintRecordCount = 0   sintPageSize = 0   sintPageNow = 0   sintPageMax = 0  End Sub    Rem ## 保存记录数的 Cookies 变量  Public Property Let strCookiesName(Value)   sstrCookiesName = Value  End Property    Rem ## 转向地址  Public Property Let strPageUrl(Value)   sstrPageUrl = Value  End Property    Rem ## 表名  Public Property Let strTableName(Value)   sstrTableName = Value  End Property    Rem ## 字段列表  Public Property Let strFieldsList(Value)   sstrFieldsList = Value  End Property    Rem ## 查询条件  Public Property Let strCondiction(Value)   If Value <> "" Then    sstrCondiction = " WHERE " & Value   Else    sstrCondiction = ""   End If  End Property    Rem ## 排序字段, 如: [ID] ASC, [CreateDateTime] DESC  Public Property Let strOrderList(Value)   If Value <> "" Then    sstrOrderList = " ORDER BY " & Value   Else    sstrOrderList = ""   End If  End Property    Rem ## 用于统计记录数的字段  Public Property Let strPrimaryKey(Value)   sstrPrimaryKey = Value  End Property    Rem ## 每页显示的记录条数  Public Property Let intPageSize(Value)   sintPageSize = toNum(Value, 20)  End Property    Rem ## 数据库连接对象  Public Property Let objConn(Value)   Set sobjConn = Value  End Property    Rem ## 当前页  Public Property Let intPageNow(Value)   sintPageNow = toNum(Value, 1)  End Property    Rem ## 页面参数  Public Property Let strPageVar(Value)   sstrPageVar = Value  End Property    Rem ## 是否刷新. 1 为刷新, 其他值则不刷新  Public Property Let intRefresh(Value)   sintRefresh = toNum(Value, 0)  End Property    Rem ## 获得当前页  Public Property Get intPageNow()   intPageNow = singPageNow  End Property    Rem ## 分页信息  Public Property Get strPageInfo()   strPageInfo = sstrPageInfo  End Property    Rem ## 取得记录集, 二维数组或字串, 在进行循环输出时必须用 IsArray() 判断  Public Property Get arrRecordInfo()   If Not sbooInitState Then    Exit Property   End If      Dim rs, sql   sql = "SELECT " & sstrFieldsList & _    " FROM " & sstrTableName & _    sstrCondiction & _    sstrOrderList      Set rs = Server.CreateObject("Adodb.RecordSet")   rs.open sql, sobjConn, 1, 1   If Not(rs.eof or rs.bof) Then    rs.PageSize = sintPageSize    rs.AbsolutePage = sintPageNow    If Not(rs.eof or rs.bof) Then     arrRecordInfo = rs.getrows(sintPageSize)    Else     arrRecordInfo = ""    End If   Else    arrRecordInfo = ""   End If   rs.close   Set rs = nothing  End Property    Rem ## 初始化记录数  Private Sub InitRecordCount()   sintRecordCount = 0   If Not(sbooInitState) Then Exit Sub   Dim sintTmp   sintTmp = toNum(request.Cookies("_xp_" & sstrCookiesName), -1)   If ((sintTmp < 0) Or (sintRefresh = 1))Then    Dim sql, rs    sql = "SELECT COUNT(" & sstrPrimaryKey & ")" & _     " FROM " & sstrTableName & _     sstrCondiction    Set rs = sobjConn.execute(sql)    If rs.eof or rs.bof Then     sintTmp = 0    Else     sintTmp = rs(0)    End If    sintRecordCount = sintTmp        response.Cookies("_xp_" & sstrCookiesName) = sintTmp   Else    sintRecordCount = sintTmp   End If  End Sub    Rem ## 初始化分页信息  Private Sub InitPageInfo()   sstrPageInfo = ""   If Not(sbooInitState) Then Exit Sub      Dim surl      surl = sstrPageUrl      If Instr(1, surl, "?", 1) > 0 Then    surl = surl & "&" & sstrPageVar & "="   Else    surl = surl & "?" & sstrPageVar & "="   End If      If sintPageNow <= 0 Then sintPageNow = 1   If sintRecordCount mod sintPageSize = 0 Then    sintPageMax = sintRecordCount / sintPageSize   Else    sintPageMax = sintRecordCount / sintPageSize + 1   End If   If sintPageNow > sintPageMax Then sintPageNow = sintPageMax      If sintPageNow <= 1 then    sstrPageInfo = "首页 上一页"   Else    sstrPageInfo = sstrPageInfo & " <a href=""" & surl & "1"">首页</a>"    sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow - 1) & """>上一页</a>"   End If      If sintPageMax - sintPageNow < 1 then    sstrPageInfo = sstrPageInfo & " 下一页 末页 "   Else    sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow + 1) & """>下一页</a> "    sstrPageInfo = sstrPageInfo & " <a href=""" & surl & sintPageMax & """>末页</a> "   End If      sstrPageInfo = sstrPageInfo & " 页次：<strong><font color=""#990000"">" & sintPageNow & "</font> / " & sintPageMax & " </strong>"   sstrPageInfo = sstrPageInfo & " 共 <strong>" & sintRecordCount & "</strong> 条记录 <strong>" & sintPageSize & "</strong> 条/页 "  End Sub    Rem ## 长整数转换  Private function toNum(s, Default)   s = s & ""   If s <> "" And IsNumeric(s) Then    toNum = CLng(s)   Else    toNum = Default   End If  End function    Rem ## 类初始化  Public Sub InitClass()   sbooInitState = True   If Not(IsObject(sobjConn)) Then sbooInitState = False   Call InitRecordCount()   Call InitPageInfo()     End Sub End Class Dim strLocalUrl strLocalUrl = request.ServerVariables("SCRIPT_NAME")  Dim intPageNow intPageNow = request.QueryString("page")  Dim intPageSize, strPageInfo intPageSize = 30  Dim arrRecordInfo, i Dim Conn f__OpenConn  Dim clsRecordInfo  Set clsRecordInfo = New Cls_PageView    clsRecordInfo.strTableName = "[myTable]"  clsRecordInfo.strPageUrl = strLocalUrl  clsRecordInfo.strFieldsList = "[ID], [Title], [LastTime]"  clsRecordInfo.strCondiction = "[ID] < 10000"  clsRecordInfo.strOrderList = "[ID] ASC"  clsRecordInfo.strPrimaryKey = "[ID]"  clsRecordInfo.intPageSize = 20  clsRecordInfo.intPageNow = intPageNow    clsRecordInfo.strCookiesName = "RecordCount"  clsRecordInfo.strPageVar = "page"    clsRecordInfo.intRefresh = 0  clsRecordInfo.objConn = Conn  clsRecordInfo.InitClass    arrRecordInfo = clsRecordInfo.arrRecordInfo  strPageInfo = clsRecordInfo.strPageInfo  Set clsRecordInfo = nothing f__CloseConn%><html><head><meta http-equiv="Content-Type" content="text/html; charset=gb2312"><title>分页测试</title><style type="text/css"><!--.PageView { font-size: 12px;}.PageView td { border-right-style: solid; border-bottom-style: solid; border-right-color: #E0E0E0; border-bottom-color: #E0E0E0; border-right-width: 1px; border-bottom-width: 1px;}.PageView table { border-left-style: solid; border-top-style: solid; border-left-color: #E0E0E0; border-top-color: #E0E0E0; border-top-width: 1px; border-left-width: 1px;}tr.Header { background: #EFF7FF; font-size: 14px; font-weight: bold; line-height: 120%; text-align: center;}--></style><style type="text/css"><!--body { font-size: 12px;}a:link { color: #993300; text-decoration: none;}a:visited { color: #003366; text-decoration: none;}a:hover { color: #0066CC; text-decoration: underline;}a:active { color: #000000; text-decoration: none;}table { font-size: 12px;}--></style></head><body><table width="100%" border="0" cellspacing="0" cellpadding="4">  <tr>  <td>&nbsp;<%= strPageInfo%></td> </tr></table><div class="PageView">  <table width="100%" border="0" cellspacing="0" cellpadding="4">    <tr class="Header">     <td>ID</td>    <td>描述</td>    <td>日期</td>  </tr> <%  If IsArray(arrRecordInfo) Then   For i = 0 to UBound(arrRecordInfo, 2) %>  <tr>    <td>&nbsp;<%= arrRecordInfo(0, i)%></td>    <td>&nbsp;<%= arrRecordInfo(1, i)%></td>    <td>&nbsp;<%= arrRecordInfo(2, i)%></td>  </tr> <%   Next  End If %></table></div><table width="100%" border="0" cellspacing="0" cellpadding="4">  <tr>   <td>&nbsp;<%= strPageInfo%></td> </tr></table><table width="100%" border="0" cellspacing="0" cellpadding="4">  <tr>     <td align="center">&nbsp;<%= getTimeOver(1)%></td>  </tr></table></body></html>转自：http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx