Sql Server 2005 row_number()分頁效能測試

   現在分頁方法大多集中在select top/not in/遊標/row_number，而select top分頁(在這基礎上還有二分法)方法似乎更受大家歡迎，這篇文章並不打算去討論是否通用的問題，本著實用的原則，花了一些時間去測試row_number()分頁的效能，感覺並不像一部分人所說的那麼雞肋，由於接觸軟體開發才十個月，方方面面的東西都要學，經驗實在有限,不足之處請原諒，測試如下：

平臺與環境：
CPU:AMD 1150 2G 單核
記憶體：1G(系統正常啟動後約佔300M空間)
硬碟：SATA 160G 8M Cache
系統：windows 2003 ent+Sql Server 2005 sp2
資料：共500萬條
-------------------------------------------------------------------
測試資料：
create table test_table
(
id   int identity(1,1) primary key not null,
cid   int  not null,
userName  varchar(50) null,
userPwd   varchar(50) null,
createTime datetime null
)
---------------------------------------------------------------------
插入記錄(cid分別插入1,2,3,4,機器實在太慢,總共只插入500萬條)：
declare @count int
set @count=1
while @count<=1000000
begin
insert into test_table(cid,userName,userPwd,createTime) values(2,'admin','admin888',getdate())
set @count=@count+1
end
-------------------------------------------------------------------------------------------------------
分頁測試程式碼：
這裡採用row_number的兩種分頁方式：分別用top和between過濾
/*row_number() 查詢方法一*/
declare @tdiff datetime
set @tdiff=getdate()
select top 20 * from(select row_number() over(order by createtime desc,id asc) as rownumber,* from test_table ) as tb where rownumber>120000
select datediff(ms,@tdiff,getdate()) as '耗時(毫秒)'

/*row_number() 查詢方法二*/
declare @tdiff datetime 
set @tdiff=getdate()
select * from(select row_number() over(order by createtime desc,id asc) as rownumber,* from test_table ) as tb where rownumber between 120000 and 120200
select datediff(ms,@tdiff,getdate()) as '耗時(毫秒)'
----------------------------------------------------------------------------------------------------------
測試方法及結果(取三次平均值)：
第一次測試，每頁顯示20條(單位：毫秒)：
索引1(聚集) id asc
索引2(非聚集) createtime desc
頁次       方法1      方法2
1                0              0
10              0              0
100            10            10
1000          65            70
1W            530           546
10W       4500           4700
20W       9.5秒         9.7秒
---------------------------------------
第二次測試，每頁顯示20條(單位：毫秒)：
索引1(聚集) id asc
索引2(非聚集) createtime desc，包含性列:cid,userName,userPwd
頁次       方法1      方法2
1                0              0
10              0              0
100            0              0
1000          13            16
1W           240         250
10W         2240       2260
20W         4436       4481
-----------------------------------------------------------------------------------------------------------------------------------------
總結及個人觀點：
由於表內記錄具有一定規律性和查詢的不確定性，在實際操作中，查詢時間會比以上資料長，查詢結果僅做參考。
1.top過濾要稍優於between過濾
2.在分頁至10W即第200W第記錄時，查詢已經要2秒以上,個人機器原因，稍微好點的電腦查詢速度可能可以提高到1秒以內。
3.分頁查詢的效率更重要的是取決於根據程式對資料庫的優化，如索引的正確建立，分割槽等因素(還在學習和研究中...)
3.如果是海量級資料，其實轉變一下思路也未嘗不可，按使用者的瀏覽習慣幾乎不會翻到千頁以後,個人感覺只要前1000頁分頁效率能接受就可以，測試1千頁以後的效率有些多餘,前臺完全只需要呈現前幾百頁即可(如部落格園只展示前200頁(目前隨筆數 568234)，淘寶只展示前100頁)，按測試的row_number效率。完全可以勝任。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-623881/，如需轉載，請註明出處，否則將追究法律責任。