Sql Server 2005 row_number()分页性能测试

h0945466

   现在分页方法大多集中在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效率。完全可以胜任。
　　
　　
　　
　　评价：
row_number分页除了节省代码外，没什么优点可言，还不如select top方式，更不用说二分法等。

你就分析下row_number写法就明白了。

select * from (
select *,row_number(order by ** desc) as yy from XXXX
)m where yy between 1 and 20 

　　先把整个表符合条件的数据都取出来放在一个临时表中，建立行号，然后再select.试问速度能快么？？？