The main idea: Use one statement to count (Count) the number of records (instead of obtaining the RecordCount attribute when querying), cache it in Cookies, and do not need to count again when jumping. Use ADO's AbsolutePage attribute to jump to the page. For convenience Called and written as a class, the main parts of the code have been explained.
Hardware environment: AMD Athlon XP 2600+, 256 DDR
Software environment: MS Windows 2000 Advanced Server + IIS 5.0 + Access 2000 + IE 6.0
Test results: The initial run time is 250 (home page) - 400 (last page) milliseconds, (after the record number is cached) the jump between pages is stable at less than 47 milliseconds. The jump from the first page to the last page does not take more than 350 milliseconds.
Applicable range : Used for ordinary paging. Not suitable for more complex queries: if the condition is "[Title] Like '%favorite%'", the query time will be greatly increased, even if the Title field is indexed, it will be useless. :(
< %@LANGUAGE = "VBScript" CODEPAGE="936"%>
<%Option Explicit%>
<%
Dim intDateStart
intDateStart = Timer()
Rem ## Open database connection
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 ## Close database connection
Rem ############################################### ################
function f__CloseConn()
If IsObject(conn) Then
conn.close
End If
Set conn = nothing
End function
Rem ############################################### ################
Rem gets execution time
Rem ############################################### ################
function getTimeOver(iflag)
Dim tTimeOver
If iflag = 1 Then
tTimeOver = FormatNumber(Timer() - intDateStart, 6, true)
getTimeOver = "Execution time: " & tTimeOver & " seconds"
Else
tTimeOver = FormatNumber((Timer() - intDateStart) * 1000, 3, true)
getTimeOver = "Execution time: " & tTimeOver & " milliseconds"
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 variable that saves the number of records
Public Property Let strCookiesName(Value)
sstrCookiesName = Value
End Property
Rem ## redirect address
Public Property Let strPageUrl(Value)
sstrPageUrl=Value
End Property
Rem ## table name
Public Property Let strTableName(Value)
sstrTableName = Value
End Property
Rem ## field list
Public Property Let strFieldsList(Value)
sstrFieldsList = Value
End Property
Rem ## query conditions
Public Property Let strCondiction(Value)
If Value <> "" Then
sstrCondiction = " WHERE " & Value
Else
sstrCondiction = ""
End If
End Property
Rem ## Sorting fields, such as: [ID] ASC, [CreateDateTime] DESC
Public Property Let strOrderList(Value)
If Value <> "" Then
sstrOrderList = " ORDER BY " & Value
Else
sstrOrderList = ""
End If
End Property
Rem ## Field used to count the number of records
Public Property Let strPrimaryKey(Value)
sstrPrimaryKey = Value
End Property
Rem ## The number of records displayed on each page
Public Property Let intPageSize(Value)
sintPageSize = toNum(Value, 20)
End Property
Rem ## Database connection object
Public Property Let objConn(Value)
Set sobjConn = Value
End Property
Rem ## Current page
Public Property Let intPageNow(Value)
sintPageNow = toNum(Value, 1)
End Property
Rem ## Page parameters
Public Property Let strPageVar(Value)
sstrPageVar = Value
End Property
Rem ## Whether to refresh. 1 means refresh, other values do not refresh.
Public Property Let intRefresh(Value)
sintRefresh = toNum(Value, 0)
End Property
Rem ## Get the current page
Public Property Get intPageNow()
intPageNow = singPageNow
End Property
Rem ## Pagination information
Public Property Get strPageInfo()
strPageInfo = sstrPageInfo
End Property
Rem ## To obtain a record set, two-dimensional array or string, you must use IsArray() to judge when performing loop output.
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 ## Initialize the number of records
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 ## Initialize paging information
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 = "Home page previous page"
Else
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & "1"">Home</a>"
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow - 1) & """>Previous page</a>"
End If
If sintPageMax - sintPageNow < 1 then
sstrPageInfo = sstrPageInfo & "Next page last page"
Else
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow + 1) & """>Next page</a> "
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & sintPageMax & """>Last page</a> "
End If
sstrPageInfo = sstrPageInfo & "Page:<strong><font color=""#990000"">" & sintPageNow & "</font> / " & sintPageMax & " </strong>"
sstrPageInfo = sstrPageInfo & "Total<strong>" & sintRecordCount & "</strong>Record<strong>" & sintPageSize & "</strong>Record/Page"
End Sub
Rem ## long integer conversion
Private function toNum(s, Default)
s = s & ""
If s <> "" And IsNumeric(s) Then
toNum = CLng(s)
Else
toNum = Default
End If
End function
Rem ## Class initialization
Public Sub InitClass()
sbooInitState = True
If Not(IsObject(sobjConn)) Then sbooInitState = False
CallInitRecordCount()
CallInitPageInfo()
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>Paging test</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>Description</td>
<td>Date</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>