最佳的数据库分页方法

一：一个老生常谈的问题。
　　　　我们知道，记录集分页是数据库处理中十分常见的问题。而当我们设计到网络数据库，就是说要考虑传输带宽问题时，分页问题就每每困扰着每一个数据库程序设计人员。

二：分页问题的解决方案汇总
　　　　说起解决的方案，每个数据库设计人员可能都会举出许多方法。但细分后，可以归为三类。一：Ado纪录集分页、二：专储记录集分页、三、数据库游标分页。
　一：著名的ADO纪录集分页。
　　　　　说到著名，因为这个可能是最简单和常见的分页方法了。（可能也是用的最多的）就是利用ADO自带的分页功能来实现分页。
　　　　　具体流程为，数据库根据查询语句返回一个完整的纪录集。然后到客户端后有客户端的游标进行分页。他们大多数由ADO自带的Recordset对象就可以实现了。可能涉及到的属性有：
recordset.pagesize:每页输出纪录集的大小
recordset.AbsolutePage:　当前输出的页　　　　（有以上的两个属性其实就可以完成分页输出了）
recordset.pagecount:　目前的总页数。
　　　　　这种方法好不好呢，有的人说是十分好，也有人说效率不高。其实这的根据实际的应用状况来确定，如果是单机数据库，或者是局域网环境，或者数据库纪录较少，则他都是很好的分页方法，而且如果不涉及到网络和更新较少的环境里，则他可以说是最好的分页方法。因为它可以先生成一个缓存记录集，以后的几页纪录都可以不通过数据库来取即可。但是，如果涉及到网络，或者是更新频繁。他就不是很实用的了。

列举程序。（我们都用网络问题来考虑）
　
nowpage=Request("nowpage")　@#当前的输出页
if　nowpage=""　or　nowpage<1　then　nowpage=1

set　rs=server.CreateObject("adodb.recordset")
rs.CursorType=1
sql="select　*　from　table1　"
rs.open　sql,strconn　(strconn为连接字段，已定义过）

rs.PageSize=20　　@#当前页的大小
if　cint(nowpage)>rs.pagecount　then　nowpage=rs.pagecount
rs.AbsolutePage=nowpage

@#然后输出当前一页的纪录
@#.............


还可以提供一下程序属性设置。
首页：nowpage=1
前页：nowpage=nowpage-1
下页：nowpage=nowpage+1
尾页：nowpage=rs.pagecount
总纪录数：rs.recordcount
总页数：rs.pagecount

二：转储纪录集分页。
这种方法诞生于网络时代，就是利用服务器端的强大处理过程，先将目标数据库存到一个临时的数据库里，并且加上一个自增字段来进行划分页面，最后将所需固定数目的纪录集传回。

优点是：只需交互一次，而且返回固定一页的纪录集。
缺点是：如果纪录集增大时则每次都需建立一个临时纪录集，也比较耗时间，但减少了网络传输量。
例子：

（
取自　Worx　英文版的<<Professional　Active　Server　Pages　3.0>>
ISBN1861002610
关键地方我已作了中文翻译
）
CREATE　PROCEDURE　usp_PagedAuthors
@iPage int,
@iPageSize int
AS
BEGIN
--　disable　row　counts
SET　NOCOUNT　ON

--　declare　variables
DECLARE　@iStart int --　start　record
DECLARE　@iEnd int --　end　record
DECLARE　@iPageCount int --　total　number　of　pages

--　create　the　temporary　table
　　　　　　　--建立临时表。

CREATE　TABLE　#PagedAuthors　　　　　　　 (
--这个自增字段十分关键，就是靠他来完成分页标示。
ID int IDENTITY,　　　　　　
au_id varchar(11) NOT　NULL　,
au_lname varchar(40) NOT　NULL　,
au_fname varchar(20) NOT　NULL　,
phone char(12) NOT　NULL　,
address varchar(40) NULL　,
city varchar(20) NULL　,
state char(2) NULL　,
zip char(5) NULL　,
contract bit NOT　NULL　
)

--　populate　the　temporary　table　　　　　　　
--先转存到上面的这个纪录集。
INSERT　INTO　#PagedAuthors　(au_id,　au_lname,　au_fname,　
phone,　address,　city,　state,　zip,　contract)
SELECT au_id,　au_lname,　au_fname,　
phone,　address,　city,　state,　zip,　contract
FROM authors


--　work　out　how　many　pages　there　are　in　total
SELECT @iPageCount　=　COUNT(*)
FROM　 authors

SELECT @iPageCount　=　CEILING(@iPageCount　/　@iPageSize)　+　1

--　check　the　page　number
IF　@iPage　<　1
SELECT　@iPage　=　1

IF　@iPage　>　@iPageCount
SELECT　@iPage　=　@iPageCount

--　calculate　the　start　and　end　records
SELECT　@iStart　=　(@iPage　-　1)　*　@iPageSize
SELECT　@iEnd　=　@iStart　+　@iPageSize　+　1

--　select　only　those　records　that　fall　within　our　page
--这条sql语句就是选取固定的纪录集。

SELECT au_id,　au_lname,　au_fname,　
phone,　address,　city,　state,　zip,　contract
FROM #PagedAuthors
WHERE ID　>　@iStart
AND ID　<　@iEnd


DROP　TABLE　#PagedAuthors

--　turn　back　on　record　counts
SET　NOCOUNT　OFF

--　Return　the　number　of　records　left
RETURN　@iPageCount
END


而输出端可以用最快类型的ADO"火线光标"顺次输出就可
<%
　　Dim　cmdAuthors
　　Dim　rsData
　　Dim　iPage
　　Dim　iLastPage
　　Dim　sQuote

　　sQuote　=　Chr(34)

　@#　get　the　requested　data
　　If　Request.QueryString("PAGE")　=　""　Then
　　　　iPage　=　1
　　Else
　　　　iPage　=　CInt(Request.QueryString("PAGE"))

　　　　If　iPage　<　1　Then
　　　　　　iPage　=　1
　　　　End　If
　　End　If

　　@#　create　the　objects
　　Set　cmdAuthors　=　Server.CreateObject("ADODB.Command")
　　Set　rsAuthors　=　Server.CreateObject("ADODB.Recordset")

　　With　cmdAuthors
　　　　.ActiveConnection　=　strConn
　　　　.CommandText　=　"usp_PagedAuthors"
　　　　.CommandType　=　adCmdStoredProc

　　　　.Parameters.Append　.CreateParameter("RETURN_VALUE",　adInteger,　_
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　adParamReturnValue)
　　　　.Parameters.Append　.CreateParameter("@iPage",　adInteger,　_
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　adParamINput,　8,　iPage)
　　　　.Parameters.Append　.CreateParameter("@iPageSize",　adInteger,　_
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　adParamINput,　8,　10)

　　　　Set　rsData　=　.Execute
　　End　With

　　@#　Create　the　table
　　@#　start　building　the　table
　　Response.Write　"<TABLE　BORDER=1><THEAD><TR>"
　　For　Each　fldF　In　rsData.Fields
　　　　Response.Write　"<TD>"　&　fldF.Name　&　"</TD>"
　　Next
　　Response.Write　"</TR></THEAD><TBODY>"

　　@#　now　loop　through　the　records
　　While　Not　rsData.EOF
　　　　Response.Write　"<TR>"
　　　　For　Each　fldF　In　rsData.Fields
　　　　　　Response.Write　"<TD>"　&　fldF.Value　&　"</TD>"
　　　　Next
　　　　Response.Write　"</TR>"
　　　　rsData.MoveNext
　　Wend
　　Response.Write　"</TBODY></THEAD></TABLE><P>"
　　@#　now　some　paging　controls
　　sMe　=　Request.ServerVariables("SCRIPT_NAME")
　　Response.Write　" <A　HREF="　&　sQuote　&　sMe　&　"?PAGE=1"　&　sQuote　&　">First　Page</A>"

　　@#　close　the　recordset　and　extract　the　number　of　records　left
　　rsData.Close
　　iLastPage　=　cmdAuthors.Parameters("RETURN_VALUE")

　　@#　only　give　an　active　previous　page　if　there　are　previous　pages
　　If　iPage　<=　1　Then
　　　　Response.Write　" <SPAN>Previous　Page</SPAN>"
　　Else
　　　　Response.Write　" <A　HREF="　&　sQuote　&　sMe　&　"?PAGE="　&　iPage　-　1　&　sQuote　&　">Previous　Page</A>"
　　End　If

　　@#　only　give　an　active　next　page　if　there　are　more　pages
　　If　iLastPage　=　iPage　Then
　　　　Response.Write　" <SPAN>Next　Page</SPAN>"
　　Else
　　　　Response.Write　" <A　HREF="　&　sQuote　&　sMe　&　"?PAGE="　&　iPage　+　1　&　　sQuote　&　">Next　Page</A>"
　　End　If

　　Response.Write　" <A　HREF="　&　sQuote　&　sMe　&　"?PAGE="　&　iLastPage　&　sQuote　&　">Last　Page</A>"

　　@#　clean　up
　　Set　rsData　=　Nothing
　　Set　cmdAuthors　=　Nothing
%>

第三种方法：服务器端游表选取纪录集的办法。
　　　　这种办法属于很有争论的办法。
　　　　它主要是用服务器端的游表选取纪录集，然后一次返回，也就是返回多个纪录集，每个纪录集就有一个纪录。然后用Recordset.nextrecord的方法来输出每一个纪录集。
　　　　国外许多网站对此进行过考证，因为第一：Recordset.nextrecord具有这种方法的ADO.游表不是最快的火线游表，第二，许多人认为采用recordset.nextrecord方法输出时其实等于和服务器端交互了一次，所以这种方法属于那种当许多人并发访问数据库时，能导致数据库访问量成倍增。。。　
　　　　　　　　　　　　　
列举程序：（作者：bigeagle）
if　exists(select　*　from　sysobjects　where　ID　=　object_id("up_TopicList"))
　　　drop　proc　up_TopicList
go

create　proc　up_TopicList　
　　　　　　　　　　　　@a_ForumID　int　,　@a_intDays　int　,　@a_intPageNo　int　,　@a_intPageSize　tinyint
　　　as
　　　　　　　declare　@m_intRecordNumber　int
　　　　　　　declare　@m_intStartRecord　　int
　　　　　　　select　@m_intRecordNumber　=　@a_intPageSize　*　@a_intPageNo
　　　　　　　select　@m_intStartRecord　=　@a_intPageSize　*　(@a_intPageNo　-　1)　+　1

　　　　　　　if　@a_intDays　=　0　　　　　　　　　　　　　　　　　　　　　　--如果不限定天数
　　　　　　　　　　begin
　　　　　　　　　　　　　　　　/*求符合条件记录数*/
　　　　　　　　　　　　　　　　select　"RecordCount"　=　count(*)　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　from　BBS　where　Layer=1　and　ForumID　=　@a_ForumID　

　　　　　　　　　　　　　　　/*输出纪录*/
　　　　　　　　　　　　　　　/*首先定义可滚动光标*/
　　　　　　　　　　　　　　　set　rowcount　@m_intRecordNumber
　　　　　　　　　　　　　　　declare　m_curTemp　Scroll　cursor　
　　　　　　　　　　　　　　　　　　　　　　　for
　　　　　　　　　　　　　　　　　　　　　　　　　　select　a.ID　,a.Title　,　d.UserName　,　a.FaceID　,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　@#ContentSize@#　=　datalength(a.Content)　,　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　@#TotalChilds@#　=　(select　sum(TotalChilds)　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　from　BBS　as　b　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　where　a.RootID　=　b.RootID)　,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　@#LastReplyTime@#　=　(select　max(PostTime)　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　from　BBS　as　c　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　where　a.RootID　=　c.RootID)
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　from　BBS　as　a　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　join　BBSUser　as　d　on　a.UserID　=　d.ID　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　where　Layer=1　and　ForumID　=　@a_ForumID　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　order　by　RootID　desc　,　Layer　,　PostTime
　　　　　　　　　　　　　　　open　m_curTemp
　　　　　　　　　　　　　　　fetch　absolute　@m_intStartRecord　from　m_curTemp
　　　　　　　　　　　　　　　while　　@@fetch_status　=　0　
　　　　　　　　　　　　　　　　　　　　　　fetch　next　from　m_curTemp

　　　　　　　　　　　　　　　set　rowcount　0　
　　　　　　　　　　　　　　　/*清场*/　　　　　　　
　　　　　　　　　　　　　　　CLOSE　m_curTemp
　　　　　　　　　　　　　　　DEALLOCATE　m_curTemp
　　　　　　　　　　end　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　else　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　--如果限定天数　　　　　　　　　　

　　　　　　　　　　begin
　　　　　　　　　　　　　　　　/*求符合条件记录数*/
　　　　　　　　　　　　　　　　select　"RecordCount"　=　count(*)　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　from　BBS　where　Layer=1　and　ForumID　=　@a_ForumID　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　and　dateadd(day　,　@a_intDays　,　PostTime)　>　getdate()　

　　　　　　　　　　　　　　　/*输出纪录*/
　　　　　　　　　　　　　　　/*首先定义可滚动光标*/
　　　　　　　　　　　　　　　set　rowcount　@m_intRecordNumber
　　　　　　　　　　　　　　　declare　m_curTemp　Scroll　cursor　
　　　　　　　　　　　　　　　　　　　　　　　for
　　　　　　　　　　　　　　　　　　　　　　　　　　select　a.ID　,a.Title　,　d.UserName　,　a.FaceID　,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　@#ContentSize@#　=　datalength(a.Content)　,　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　@#TotalChilds@#　=　(select　sum(TotalChilds)　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　from　BBS　as　b　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　where　a.RootID　=　b.RootID)　,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　@#LastReplyTime@#　=　(select　max(PostTime)　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　from　BBS　as　c　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　where　a.RootID　=　c.RootID)
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　from　BBS　as　a　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　join　BBSUser　as　d　on　a.UserID　=　d.ID　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　where　Layer=1　and　ForumID　=　@a_ForumID　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　and　dateadd(day　,　@a_intDays　,　PostTime)　>　getdate()　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　order　by　RootID　desc　,　Layer　,　PostTime
　　　　　　　　　　　　　　　open　m_curTemp
　　　　　　　　　　　　　　　fetch　absolute　@m_intStartRecord　from　m_curTemp
　　　　　　　　　　　　　　　while　　@@fetch_status　=　0　
　　　　　　　　　　　　　　　　　　　　　　fetch　next　from　m_curTemp

　　　　　　　　　　　　　　　set　rowcount　0　
　　　　　　　　　　　　　　　/*清场*/　　　　　　　
　　　　　　　　　　　　　　　CLOSE　m_curTemp
　　　　　　　　　　　　　　　DEALLOCATE　m_curTemp
　　　　　　　　　　end　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
go


注：若在asp中调用存储过程的command对象为cm，则set　rs=cm.execute，然后用set　rs=rs.nextrecordset取下一条记录。
三：测试结果。

看到了这么多的分页方法。那么那种最好呢，最好的分页方法是什么呢？
还是做个测试吧。
测试工具：Microsoft　Web　Application　Stress　Tool　1.1
测试平台：Win2000　server　中文版　+　IIS5.0　+　sql　server　7.0
数据纪录：8000条　　（非相同的股票历史纪录集）
模拟环境：56K　model　/　2M专线　/　10兆专线　
测试次数：3次


测试结果：服务器游标　>==　存储过程分页　>　>　Ado分页　
(符号　>==　:表示基本上相同，但有时稍大，　符号：>　>　远远大于　）
看来，前两者差别不大，而且在多纪录集时，服务器端游标比存储过程稍大一些。但都比Ado游标分页效率要好的多。

那么什么是最好的分页方法呢，我们理想的分页方法是什么呢？
其实就是：客户端传递一个页码过去，然后服务器端直接通过一次查询就生成所需的一页的记录集，并且以一个纪录集的形式返回给客户端。那么这种放法有没有呢？我可以告诉大家，有，而且经过我的测试，确实是效率最好的一种方法。下次我们就谈谈这种理论上最好的分页方法。。。。。。。。。

原文转自：http://www.ltesting.net