主要想法: 用一條語句統計(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> <%= 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
為 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>