Sql Server 2005 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效率。完全可以勝任。

評價：

先把整個表符合條件的數據都取出來放在一個臨時表中，建立行號，然后再select.試問速度能快么

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈