快捷導(dǎo)航

分頁(yè)實(shí)現(xiàn)方法的性能比較

更新時(shí)間：2008年11月24日 20:47:17 作者：

我們先給出幾種主要的分頁(yè)方法和核心語(yǔ)句,然后直接給出結(jié)論,有興趣的讀者可以看看后面的數(shù)據(jù)

幾種常用存儲(chǔ)過(guò)程分頁(yè)方法
TopN方法
select Top(@PageSize) from TableName where ID Not IN
(Select Top ((@PageIndex-1)*@PageSize) ID from Table Name where .... order by ... )
where .... order by ...
臨時(shí)表

復(fù)制代碼代碼如下:

declare @indextable table(id int identity(1,1),nid int,PostUserName nvarchar(50)) 
declare @PageLowerBound int 
declare @PageUpperBound int 
set @PageLowerBound=(@pageindex-1)*@pagesize--下限 
set @PageUpperBound=@PageLowerBound+@pagesize--上限 
set rowcount @PageUpperBound 
insert into @indextable(nid,PostUserName) select ReplyID,PostUserName from TableName order by ...... 
select * from TableName p,@indextable t where p.ID=t.nid 
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id 

CTE--2005新語(yǔ)法,類(lèi)似臨時(shí)表,但是生命周期稍微不同,這里只是他的一個(gè)運(yùn)用
with cte_temp--定義零時(shí)表,PageIndex是一個(gè)計(jì)算字段,儲(chǔ)存了搜索結(jié)果的頁(yè)號(hào)
As (ceiling((Row_Number() over(order by .... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)
select * from cte_temp where pageindex=@pageindex-1;
結(jié)論:
TopN在小頁(yè)數(shù)下最快,如果在10頁(yè)以下,可以考慮用它,CTE和臨時(shí)表時(shí)間很穩(wěn)定,CTE消耗的時(shí)間比臨時(shí)表多,但是不會(huì)引起tempdb的暴漲和IO增加
性能比較
試驗(yàn)環(huán)境:win2003server,Sqlserver2005,庫(kù)大小2,567,245行,沒(méi)有where子句,試驗(yàn)時(shí)每頁(yè)大小50,頁(yè)碼作為變量
取0,3,10,31,100,316,1000,3162...頁(yè),也就是10的指數(shù),試驗(yàn)結(jié)果如下
頁(yè)數(shù) TopN CTE 臨時(shí)表(有緩存) 臨時(shí)表(無(wú)緩存)
公司正在使用的存儲(chǔ)過(guò)程 CTE改進(jìn)
1 3 12 10 101 457 7302
3 15 7 79 5524 464 7191
10 127 5504 88 3801 464 6116
32 588 9672 122 3601 976 7602
100 4680 9738 166 4235 486 7151
316 45271 9764 323 3867 522 7255
1000 Null 9806 869 2578 635 8948
3162 Null 9822 2485 4110 12460 8210
10000 Null 9754 7812 11926 14250 7359
31623 Null 9775 18729 33218 15249 7511
100000 Null Null 31538 55569 17139 6124
數(shù)據(jù)解釋和分析
臨時(shí)表分為有沒(méi)有緩存兩種時(shí)間,CTE就是上面的方法,CTE改進(jìn)只是把選入CTE臨時(shí)表的列數(shù)減少了,只選取了頁(yè)號(hào)和主鍵,Null表示時(shí)間無(wú)法計(jì)算(時(shí)間太長(zhǎng)),數(shù)據(jù)單位是毫秒.
從上面的數(shù)據(jù)可以看到,TopN在前32頁(yè)都是有優(yōu)勢(shì)的,但是頁(yè)數(shù)增大后,性能降低很快,CTE改進(jìn)比CTE有所進(jìn)步,平均進(jìn)步兩秒左右,但是還是比臨時(shí)表慢,但是考慮臨時(shí)表會(huì)增大日志文件的大小,引起大量IO,CTE也就有他自己的優(yōu)勢(shì),公司現(xiàn)在正在使用的存儲(chǔ)過(guò)程效率不錯(cuò),但是在頁(yè)碼靠后的情況下性能會(huì)降低