平台与环境:
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方式,更不用说二分法等。先把整个表符合条件的数据都取出来放在一个临时表中,建立行号,然后再select.试问速度能快么