SQL Server分页优化（一）

一、利用新特性

SQL Server 在2012版本中 提供了一种新的分页方式 fetch next
相比以前开窗函数来看有什么区别呢？ 我用 sql server 系统表产生笛卡尔积 做了一个测试
先来看一下数据量

select count(*) from syscolumns as a, syscolumns as b

用sql server的系统表 syscolumns 做了一个笛卡尔，行数达到了 5亿

1、标准的开窗函数实现分页

set statistics time on;
declare @pageindex  int --页数
declare @pagesize int --每页记录数
set  @pageindex = 20
set  @pagesize = 100

select top(@pagesize) * 
from (select row_number() 
over(order by a.id asc) as rownumber,a.* 
from syscolumns as a,syscolumns as b ) temp_row
where rownumber>((@pageindex-1)*@pagesize);

上面语句每页100条记录。 我们查询20页的内容，用了1.2秒

2、2012新特性分页

set statistics time on;
declare @pageindex  int --页数
declare @pagesize int --每页记录数
set  @pageindex = 20
set  @pagesize = 100

select a.* from syscolumns as a, syscolumns as b
order by a.id
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;

利用新特性fetch net 只快了30毫秒 同样花了1.2秒。

但我们现在把分页增加，比如我们取第200000页的数据

set statistics time on;
declare @pageindex  int --页数
declare @pagesize int --每页记录数
set  @pageindex = 200000
set  @pagesize = 100

select top(@pagesize) * 
from (select row_number() 
over(order by a.id asc) as rownumber,a.* 
from syscolumns as a,syscolumns as b ) temp_row
where rownumber>((@pageindex-1)*@pagesize);

用了3.9S
再来看用 fetch net 分页

set statistics time on;
declare @pageindex  int --页数
declare @pagesize int --每页记录数
set  @pageindex = 200000
set  @pagesize = 100

select a.* from syscolumns as a, syscolumns as b
order by a.id
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;

用了1.6s

在分页数较大的时候。fetch next 的优势就慢慢体现出来了。即使是更小的时候 时间也更快。

结论。如果你是用的sql server2012以上版本。建议还是采用 fetch next 来进行分页吧。