|
|
|
|
最佳的数据库分页方法 |
|
|
|
作者：未知 来源：月光软件站 加入时间：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游标分页效率要好的多。
那么什么是最好的分页方法呢，我们理想的分页方法是什么呢？
其实就是：客户端传递一个页码过去，然后服务器端直接通过一次查询就生成所需的一页的记录集，并且以一个纪录集的形式返回给客户端。那么这种放法有没有呢？我可以告诉大家，有，而且经过我的测试，确实是效率最好的一种方法。下次我们就谈谈这种理论上最好的分页方法。。。。。。。。。
|
|
相关文章：相关软件： |
|
本类阅读TOP10