The main idea: Use one statement to count the number of records (without obtaining the recordcount attribute during query), cache it in cookies, and do not need to count again when jumping. Use the absolutepage attribute of ado 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.
Scope of application: 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 holds 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 ## Get the 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
dimrs, 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
setrs=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>Homepage</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> records<strong> & sintpagesize & </strong> records/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
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
dimclsrecordinfo
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
setclsrecordinfo=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>