|
|
最佳的数据库分页方法 |
|
|
作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站 |
一:一个老生常谈的问题。 我们知道,记录集分页是数据库处理中十分常见的问题。而当我们设计到网络数据库,就是说要考虑传输带宽问题时,分页问题就每每困扰着每一个数据库程序设计人员。
二:分页问题的解决方案汇总 说起解决的方案,每个数据库设计人员可能都会举出许多方法。但细分后,可以归为三类。一: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游标分页效率要好的多。
那么什么是最好的分页方法呢,我们理想的分页方法是什么呢? 其实就是:客户端传递一个页码过去,然后服务器端直接通过一次查询就生成所需的一页的记录集,并且以一个纪录集的形式返回给客户端。那么这种放法有没有呢?我可以告诉大家,有,而且经过我的测试,确实是效率最好的一种方法。下次我们就谈谈这种理论上最好的分页方法。。。。。。。。。

|
|
相关文章:相关软件: |
|