分享SQL2000-2012三种分页方式测试比较

[山寨DBA]

SQLSERVER2012 出新分页功能啦！！！
近两天我在自己工作机的PC（没有并发，单一线程）上做了SqlServer 2000/ (2005/2008)/2012三个版本下的分页性能比较。

大致可得出以下结果：
1、表数据量200W以内：SQLServer2012 的offset/fetch分页性能和SQLServer2005 Row_number的分页性能（仅考虑出结果速度）基本没区别（难分高下），略高于（大约10%）SQL2000的TOP分页性能。
2、表数据量2000W左右：SQLServer2012 的offset/fetch分页性能略高于SQLServer2005 Row_number的分页性能，主要体现在IO上，但是两者性能可算是远高于（大约25%）SQL2000的TOP分页性能。
3、执行计划2012比2005简单，2005比2000简单，学习简易程度，2012最容易实现。
特此分享一下，下面是我的测试脚本，有兴趣可以自己也试试
测试环境：
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

/*
	功能：生成测试数据.
*/

create table Test_paging(
	id int identity(1,1) not null primary key,
	TestNumber int not null,
	TestName varchar(20) not null,
	TestDept varchar(10) not null,
	TestDate datetime not null
)
go

with tep(Number,Name,Dept,Date) as
(
	select 1,cast('0_testname' as varchar(20)),cast('0_DBA' as varchar(10)),getdate()
	union all
	select Number+1,cast(cast(Number as varchar(20))+'_testname' as varchar(20)),cast(cast(Number/500 as varchar(10))+'_DBA' as varchar(10)) ,getdate()
	from tep
	where Number<=20000000
)
insert into Test_paging(TestNumber,TestName,TestDept,TestDate)
select Number,Name,Dept,Date from tep option(maxrecursion 0)

--添加索引（我有测试没有索引的情况，2012的优势更加明显，但是我们的数据库不可能不建索引的，故可忽略没有索引的情况）
create nonclustered index IX_TestDept on Test_paging(
	TestDept
) include
(
	TestName,TestDate
) 
go

/*
	功能：测试2012版本中offset/fetch分页.
*/

dbcc dropcleanbuffers
dbcc freeproccache

set statistics io on
set statistics time on
set statistics profile on

declare	
	@page int,	--第@page页
	@size int,	--每页@size行
	@total int	--总行数

select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA' 

select 
	TestName,TestDept,TestDate,@total
from 
	Test_paging
where 
	TestDept = '1000_DBA' 
order by id offset (@page-1)*@size rows fetch next @size rows only

set statistics io off
set statistics time off
set statistics profile off

/*
	功能：测试2005/2008版本中row_number分页.
*/

dbcc dropcleanbuffers
dbcc freeproccache

set statistics io on
set statistics time on
set statistics profile on

declare	
	@page int,	--第@page页
	@size int,	--每页@size行
	@total int

select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'

select TestName,TestDept,TestDate,@total from
(
	select 
		TestName,TestDept,TestDate,row_number() over(order by ID) as num 
	from 
		Test_paging
	where 
		TestDept = '1000_DBA'
) test where num between (@page-1)*@size+1 and @page*@size order by num 

set statistics io off
set statistics time off
set statistics profile off

/*
	功能：测试2000版本中top分页.
*/

dbcc dropcleanbuffers
dbcc freeproccache

set statistics io on
set statistics time on
set statistics profile on

declare	
	@page int,	--第@page页
	@size int,	--每页@size行
	@total int	--总行数

select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA' 


select TestName,TestDept,TestDate,@total from
(
	select top(@size) id,TestName,TestDept,TestDate from 
	(
		select top(@page*@size) id,TestName,TestDept,TestDate
		from Test_paging 
		where TestDept = '1000_DBA'
		order by id
	)temp1 order by id desc
)temp2 order by id 

set statistics io off
set statistics time off
set statistics profile off

[guguda2008]

引用 7 楼 hwhmh2010 的回复:

[quote=引用 5 楼 guguda2008 的回复:] offset是好东西，可惜2012没推广开来。 一般不会做2000W级别数据的分页，直接分表了。 如果一定要做，不如直接建立递增列建索引的好。

不晓得微软是咋地啦，不停的开发新功能，开发出来又不非常投入的推广，很是伤啊。。。2014的always on貌似也是这样，刚开始很火爆，现在又杳无声息了。。。 offset没有推广开貌似情有可原，毕竟现在直接用数据库分页的已经很少了，涉及到分页的情况一般都用程序去做了，直接把结果丢给UI，用程序去分页，毕竟这样可以降低不少数据库压力。。。[/quote] always on我认为主要是因为需要和windows系统本身的功能绑定，所以会用的人很少，我几乎不认识对win server很了解的网管，要是再加上对sql server很熟悉，那基本就没有了。

[chen870201]

好像已经比以前简单多了

[山寨DBA]

引用 8 楼 guguda2008 的回复:

always on我认为主要是因为需要和windows系统本身的功能绑定，所以会用的人很少，我几乎不认识对win server很了解的网管，要是再加上对sql server很熟悉，那基本就没有了。

有道理，其实用win 环境搭群集的本来就很少了。。。

[还在加载中灬]

还没装2012，先收藏，谢楼主分享

[發糞塗牆]

[山寨DBA]

引用 2 楼 DBA_Huangzj 的回复:

黄版主，书卖的如何呀？

[發糞塗牆]

未知，刚换工作没时间关注这个事情

[guguda2008]

offset是好东西，可惜2012没推广开来。 一般不会做2000W级别数据的分页，直接分表了。 如果一定要做，不如直接建立递增列建索引的好。

[山寨DBA]

引用 5 楼 guguda2008 的回复:

offset是好东西，可惜2012没推广开来。 一般不会做2000W级别数据的分页，直接分表了。 如果一定要做，不如直接建立递增列建索引的好。

我当时就测了四种情况： 200W级数据有索引和无索引，没有索引的时候offset和row_number以及top比起来，都有绝对优势（IO明显提高），返回结果最快，可惜现在的关系型数据库已经离不开索引了。。。有索引的时候200W级的量offset和row_number确实真是难分高下。。。 2000W级的量的时候不管有没有索引，offset的效率都有明显体现。。。

[山寨DBA]

引用 5 楼 guguda2008 的回复:

offset是好东西，可惜2012没推广开来。 一般不会做2000W级别数据的分页，直接分表了。 如果一定要做，不如直接建立递增列建索引的好。

不晓得微软是咋地啦，不停的开发新功能，开发出来又不非常投入的推广，很是伤啊。。。2014的always on貌似也是这样，刚开始很火爆，现在又杳无声息了。。。 offset没有推广开貌似情有可原，毕竟现在直接用数据库分页的已经很少了，涉及到分页的情况一般都用程序去做了，直接把结果丢给UI，用程序去分页，毕竟这样可以降低不少数据库压力。。。

[卖水果的net]

top N 的这种方式对比 row_number ： top N 在前面的页速度还可以，越向后速度越慢， 200W 的数据，查询最后几页 ， 和前几页，性能要差上百倍。 row_number ，每页的效率差不多，比较稳定。

[xiaoxiangqing]

[Delta]

谢谢分享。厉害啊。

[程序员鼓励师]

[SchoolBoyGary]

[山寨DBA]

引用 12 楼 wmxcn2000 的回复:

top N 的这种方式对比 row_number ： top N 在前面的页速度还可以，越向后速度越慢， 200W 的数据，查询最后几页 ， 和前几页，性能要差上百倍。 row_number ，每页的效率差不多，比较稳定。

[万载馨辰]

引用 7 楼 hwhmh2010 的回复:

[quote=引用 5 楼 guguda2008 的回复:] offset是好东西，可惜2012没推广开来。 一般不会做2000W级别数据的分页，直接分表了。 如果一定要做，不如直接建立递增列建索引的好。

不晓得微软是咋地啦，不停的开发新功能，开发出来又不非常投入的推广，很是伤啊。。。2014的always on貌似也是这样，刚开始很火爆，现在又杳无声息了。。。 offset没有推广开貌似情有可原，毕竟现在直接用数据库分页的已经很少了，涉及到分页的情况一般都用程序去做了，直接把结果丢给UI，用程序去分页，毕竟这样可以降低不少数据库压力。。。[/quote] 直接用数据库分页的已经很少????? 我书读得少，你不要骗我····

[--小F--]

分页还是需要经常使用的，但是一般都是在数据过滤后的情况下。