IDC资讯中心
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
%>
今天改进完毕，性能超过网上可见的同类（未改为存储过程）
