模板
教程
環境
性能
功能
管理
最佳的數據庫分頁方法
發表于：2007-06-30來源：作者：點擊數：
標簽：
一：一個老生常談的問題。 我們知道，記錄集分頁是 數據庫 處理中十分常見的問題。而當我們設計到 網絡 數據庫，就是說要考慮傳輸帶寬問題時，分頁問題就每每困擾著每一個數據庫程序設計人員。 二：分頁問題的 解決方案 匯總 說起解決的方案，每個數據庫設計
一：一個老生常談的問題。
我們知道，記錄集分頁是數據庫處理中十分常見的問題。而當我們設計到網絡數據庫，就是說要考慮傳輸帶寬問題時，分頁問題就每每困擾著每一個數據庫程序設計人員。
二：分頁問題的解決方案匯總
說起解決的方案，每個數據庫設計人員可能都會舉出許多方法。但細分后，可以歸為三類。一：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條 ?。ǚ窍嗤墓善睔v史紀錄集）
模擬環境：56K model / 2M專線 / 10兆專線
測試次數：3次
測試結果：服務器游標 >== 存儲過程分頁 > > Ado分頁
(符號 >== :表示基本上相同，但有時稍大， 符號：> > 遠遠大于?。?
看來，前兩者差別不大，而且在多紀錄集時，服務器端游標比存儲過程稍大一些。但都比Ado游標分頁效率要好的多。
那么什么是最好的分頁方法呢，我們理想的分頁方法是什么呢？
其實就是：客戶端傳遞一個頁碼過去，然后服務器端直接通過一次查詢就生成所需的一頁的記錄集，并且以一個紀錄集的形式返回給客戶端。那么這種放法有沒有呢？我可以告訴大家，有，而且經過我的測試，確實是效率最好的一種方法。下次我們就談談這種理論上最好的分頁方法。。。。。。。。。
