Asp：Access 2000 数据库 80 万记录通用快速分页类 [摘]

热点排行

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

类别：Asp 点击：0 评论：0 推荐：

作者:萧月痕(xiaoyuehen)
地址:http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx
转贴请包含相关信息, 谢谢.

主要思路: 用一条语句统计(Count)出记录数(而不在查询时获得 RecordCount 属性), 缓存在 Cookies 中, 跳转时就不用再次统计, 分页跳转链接也由此来, 使用 ADO 的 AbsolutePage 属性设置当前页面, 返回intPageSize 条记录. 为方便调用而写成类, 代码主要地方已有说明

硬件环境: 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">  </style> <style type="text/css">  </style> </head> <body> <table width="100%" border="0" cellspacing="0" cellpadding="4"> <tr> <td> <%= 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> <%= arrRecordInfo(0, i)%></td> <td> <%= arrRecordInfo(1, i)%></td> <td> <%= arrRecordInfo(2, i)%></td> </tr> <% Next End If %> </table> </div> <table width="100%" border="0" cellspacing="0" cellpadding="4"> <tr> <td> <%= strPageInfo%></td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="4"> <tr> <td align="center"> <%= getTimeOver(1)%></td> </tr> </table> </body> </html> 这里将所有代码集中在一起是为了叙述和测试方便, 具体应用你可以将类放在单独一个文件, 然后多处调用. 请将适当的时候判断用户是否进行请求, 更改 intRefresh, 以便告知分页类是否重新统计记录数.

本文地址：http://com.8s8s.com/it/it7413.htm