现在分页方法大多集中在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.试问速度能快么???
相关推荐
SQL ROW_NUMBER()分页比较
设置sqlServer使用ROW_NUMBER时不排序 –1.看到NHibernate是这样写的分页,感觉写起来比较容易理解(应该不会有效率问题吧?) –with只是定一个别名? [sql] with query as (select ROW_NUMBER() over(order by ...
本文为大家分享了SQL Server使用row_number分页的实现方法,供大家参考,具体内容如下 1、首先是 select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1 生成带序号的集合 2、再查询该集合的 ...
利用SQL 2005中的Row_number() 进行数据分页
分页存储过程,仅适用于Sql2005以上,使用 ROW_NUMBER()函数用于多表分页查询,可以分组查询
通用存储过程分页(使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况)性能分析
SQL Server 2005中ROW_NUMBER()函数在存储过程分页中的应用.pdf
主要介绍了SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同的相关资料,需要的朋友可以参考下
ROW_NUMBER()函数将针对SELECT语句返回的每一行,从1开始编号,赋予其连续的编号。在查询时应用了一个排序标准后,只有通过编号才能够保证其顺序是一致的,当使用ROW_NUMBER函数时,也需要专门一列用于预先排序以...
C#拼接SQL语句,SQL Server 2005+,多行多列大数据量情况下,使用ROW_NUMBER实现的高效分页排序
SQL2005利用ROW_NUMBER() OVER实现分页功能,需要的朋友可以参考下
sqlserver2005使用row_number() over分页的实现方法,需要的朋友可以参考下。
采用另外一种方式来进行分页。。RowNumber方式或许更快,方式更好点
在SqlServer2005中,提供了一个row_number()的函数,我们经常用它做DataBase数据分页.
本篇文章是对数据库分页的两种方法对比(row_number()over()和top的对比)进行了详细的分析介绍,需要的朋友参考下
sql存储过程分页和利用sql2005的Row_Number分页