access 测试地址：http://demo-asp.com/demo/works_list.asp
基本不受记录大小限制，15毫秒左右（access） sql存储过程的 几毫秒或0毫秒
http://www.joekoe.com/forum_view.asp?forum_id=3&view_id=54183
<!--分页数据列表过程，完全本人原创，敬请改进，盗版不纠 -->
<!--复制请保留这个：author:CUIWL(QQ:27086712 HOMEPAGE:WWW.DEMO-ASP.COM) -->
<%
sub works_list(pagesize,nsort,table,field,sqlwhere)
dim recordcount,pagecount,pageno,i
recordcount=application("recordcount"&sqlwhere)
if recordcount="" then
sql="select count(id) from works "&sqlwhere
set rs=conn.execute(sql)
recordcount=rs(0)'取得记录总数
application("recordcount"&sqlwhere)=recordcount
end if
pagecount=((recordcount-1)\pagesize)+1'总页数
pageno=trim(request("pageno"))'当前页
if isempty(pageno) or not isnumeric(pageno) then
pageno=1
else
pageno=pageno
end if
if pageno<1 then
pageno=1
end if
if pageno*10 >= pagecount*10 then
pageno=pagecount
end if
if recordcount>0 then
call get_pageid(1,100,pagesize,pageno,table,sqlwhere,pagecount)
end if
response.write "<table width='100%'><tr height=30 ><td width=50>序号</td><td width=230>作品名称</td><td width=80>作者</td><td width=100>创作日期</td></tr>"
i=0
If dblink=1 then
Sql="select top "&pagesize&" "&field&" from "&table&" "&sqlwhere
set rs=conn.execute(sql)
else
cmd.commandtext="page_list"
cmd.parameters("@pagesize")=pagesize
cmd.parameters("@table")=table
cmd.parameters("@field")=field
cmd.parameters("@sqlwhere")=sqlwhere
set rs=cmd.execute
end if
do while not rs.eof
i=i+1
response.write "<tr height=22><td >"&i&"</td><td><a href='works_view.asp?id="&rs("id")&"'>"&codehtml(rs("name"),1,18)&"</a></td><td><a href='author_view.asp?id="&rs("authorid")&"'>"&codehtml(rs("author"),1,0)&"</a></td><td>"&year(rs("finishdate"))&"年"&month(rs("finishdate"))&"月"&day(rs("finishdate"))&"日</td></tr>"
rs.movenext
loop
response.write "<form NAME='pagelist' method=get action='?'><tr height=30><td colspan=6 ><div align=center>共<font color=#FF0000>"&pagecount&"</font>页<font color=#FF0000>"&recordcount&"</font>条数据， <a href='?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno=1'>首页</a> <a href='?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno="&pageno-1&"'>上一页</a> <a href='?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno="&pageno+1&"'>下一页</a> <a href='?s_key="&s_key&"&s_field="&s_field&"&nsort="&nsort&"&author="&authorid&"&id="&sortid&"&pageno="&pagecount&"'>尾页</a> "
response.write "跳转到第<input NAME='pageno' type=text size=3 maxlength='10' value="&pageno&">页 "&_
"<input type='submit' value='go'>"&_
"<input NAME='s_key' type=hidden value='"&s_key&"'>"&_
"<input NAME='s_field' type=hidden value='"&s_field&"'>"&_
"<input NAME='nsort' type=hidden value='"&nsort&"'>"&_
"<input NAME='author' type=hidden value="&authorid&">"&_
"<input NAME='id' type=hidden value="&sortid&">"&_
"</div></td></tr></form></table>"
end sub
'****************************************取得页边界*********************************************
sub get_pageid(n,k,pagesize,pageno,table,sqlwhere,pagecount)
dim pageid
select case n
case 1
redim pageid(pagecount)
if isempty(application("pageid"&sqlwhere)) then
set rs=server.createobject("adodb.recordset")
rs.open "select id from "&table&" "&sqlwhere,conn,0,1
if not rs.eof then
dim i
for i=0 to pagecount-1
pageid(i)=rs(0)
rs.move pagesize
next
application.Lock()
application("pageid"&sqlwhere)=pageid
application.UnLock()
end if
else
pageid=application("pageid"&sqlwhere)
end if
select case pageno
case pagecount
sqlwhere=sqlwhere&" and id>="&pageid(pageno-1)
case else
sqlwhere=sqlwhere&" and id>="&pageid(pageno-1)&" and id<"&pageid(pageno)
end select
case 2
redim pageid(k-1)
dim j'第几段
'根据pageno算出j的值
j=(pageno-1)\k
if isempty(application("pageid"&j&sqlwhere)) then
set rs=server.createobject("adodb.recordset")
if j=0 then
rs.open "select top "&pagesize*k&" id from "&table&" "&sqlwhere&" order by id desc",conn,0,1
else
rs.open "select top "&pagesize*k&" id from "&table&" "&sqlwhere&" and id <(select min(id) from (select top "&pagesize*k*j&" id from "&table&" "&sqlwhere&" order by id desc) as tb) order by id desc",conn,0,1
end if
if not rs.eof then
'分割执行，pagecount\k+1段，每段K页,算出每页pageid最大id
if j=pagecount\k then
i=0
do while not rs.eof
pageid(i)=rs(0)
i=i+1
rs.move pagesize
loop
else
for i=0 to k-1
pageid(i)=rs(0)
rs.move pagesize
next
end if
application.Lock()
application("pageid"&j&sqlwhere)=pageid
application.UnLock()
end if
else
pageid=application("pageid"&j&sqlwhere)
end if
if pageno mod k=0 or pageno=pagecount then
sqlwhere=sqlwhere&" and id<="&pageid(pageno-j*k-1)&" order by id desc"
else
sqlwhere=sqlwhere&" and id<="&pageid(pageno-j*k-1)&" and id>"&pageid(pageno-j*k)
end if
end select
end sub
'****************************************************************
sub insert_datas(n)
dim i
for i=1 to n
conn.execute("insert into works SELECT top 100000 name, remark1, remark2, sort, img1, img2, finishdate, putdate, demourl, downurl, is_open, authorid, author,userid,username, seenum, power, is_top FROM works)")
next
end sub
%>
今天改进完毕，性能超过网上可见的同类（未改为存储过程）
|
本类阅读TOP10