程序员人生 网站导航

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

栏目:Sqlserver时间:2013-12-13 05:18:12
现在分页方法大多集中在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.试问速度能快么

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐