Sql Server 2012 分页方法分析(offset and fetch)

复制代码 代码如下:set statistics profile
on set statistics io on set statistics time on go 你执行的SQL语句 复制代码 代码如下:go set statistics profile off
set statistics io off set statistics time off 执行完后点消息即可。

1:下面这种是比较简单的查询方法,通过查询前的时间和查询后的时间差来计算的
复制代码 代码如下: declare @begin_date
datetime declare @end_date datetime select @begin_date = getdate()
这里写上你的语句… select @end_date = getdate() select
datediff(ms,@begin_date,@end_date) as ‘用时/毫秒’
2:下面这种方法比较全面,将执行每个语句时采取的步骤作为行集返回,通过层次结构树的形式展示出来
复制代码 代码如下: set statistics profile
on set statistics io on set statistics time on go 这里写上你的语句… go
set statistics profile off set statistics io off set statistics time off

其中 offset and fetch 最重要的新特性是 用来 分页,既然要分析
分页,就肯定要和之前的分页方式来比较了,特别是 Row_Number()
了,在比较过程中,发现了蛮多,不过最重要的,通过比较本质,得出了优劣,也和大家一起分享下。
准备工作,建立测试表:Article_Detail,主要是用来存放一些文章信息,测试的时间,都是从网易上面转载的新闻,同时,测试表数据字段类型是比较均匀的,为了更好的测试,表结构如下图:
内容:数据量:129,991 条记录 语法分析 1. NTILE() 的分页方法 NTILE()
方法可以用来分页,但是应用场景十分的狭窄,并且性能差劲,和 Row_Number()
与 offset fetch
分页比起来没有任何优势,也只有在只读表上面分页的话,还是比较合适的;虽然不好用,但是还能来分页的,所以只简单的介绍下。
语法: NTILE (integer_expression) OVER ( [ partition_by_clause ]
order_by_clause ) 将有序分区中的行分发到指定数目的组中。
各个组有编号,编号从一开始。 对于每一个行,NTILE
将返回此行所属的组的编号。 测试中用到的 Sql 语句 : 复制代码 代码如下: set statistics time on set
statistics io on set statistics profile on; with #pager as ( select
ID,Title,NTILE(8666) OVER(Order By ID) as pageid from Article_Detail )
select ID,Title from #pager where pageid=50 set statistics profile on;
其中上述数字中的 8666 是根据 RowCount / Pagesize
计算出来的,不过多介绍,可以自行参考 MSDN的 2. ROW_NUMBER() 的分页方法
在 Sql Server 2000 之后的版本中,ROW_NUMBER()
这种分页方式一直都是很不错的,比起之前的游标分页,性能好了很多,因为
ROW_NUMBER()
并不会引起全表扫表,但是,语法比较复杂,并且,随着页码的增加,性能也越来越差。
语法 : ROW_NUMBER ( ) OVER ( [澳门金沙vip, PARTITION BY value_expression , …
[ n ] ] order_by_clause ) 测试中用到的 Sql 语句: 复制代码 代码如下: dbcc freeproccache dbcc
dropcleanbuffers set statistics time on set statistics io on set
statistics profile on; with #pager as ( select ID,Title,ROW_NUMBER()
OVER(Order By ID) as rowid from Article_Detail ) select ID,Title from
#pager where rowid between (15 * (50-1)+1) and 15 * 50 set statistics
profile off; 3. Offset and Fetch 的分页方法 语法: OFFSET {
integer_constant | offset_row_count_expression } { ROW | ROWS }
FETCH { FIRST | NEXT } { integer_constant |
fetch_row_count_expression } { ROW | ROWS } ONLY 从语法可以看出来
两个方法 后面不但能接 intege 类型的参数,还能接 表达式的,比如 1*2 +3
之类的,同时, Row 或者 Rows 是不区分大小写和单复数的哦 在看测试用的 Sql
语句,真的是简洁的不能再简洁了,看两遍都能记住的语法,分页可以如此的简洁:
复制代码 代码如下: dbcc freeproccache
dbcc dropcleanbuffers set statistics time on set statistics io on set
statistics profile on; select ID,Title from Article_Detail order by id
OFFSET (15 * (50-1)) ROW FETCH NEXT 15 rows only set statistics profile
off; 一句就搞定! 性能比较 1. NTILE() 的执行计划
从执行计划中,就可以看出来,进行了一次全表扫表,两次 Nested Loops
,还有无数其他运算,就一次全表扫表,就知道性能之差了 2. ROW_NUMBER()
的执行计划 从执行计划中可以看出来, 聚集索引扫描占用了100%
的资源,但是通过 EstimateRows = 100 和 Rows = 750
可以看出来,并没有进行全表扫描,并且IO 操作很小,所以性能还是很不错的 3.
Offset and Fetch 的 执行计划执行计划只有3行,并且占用资源 100% 的IO 操作
,EstimateRows = 100 和 Rows = 750 是和 ROW_NUMBER()
完全一样的,但是其他的一些操作却少了很多,也就是说,并没有全表扫描,并降低了CPU
的消耗。 综合比较: 在 Sql Server 2012 里面,分页方法中,Offset and
Fetch 同 ROW_NUMBER() 比较起来,无论是性能还是语法,都是有优势的。
但是性能方面,优势并不是太大,两者 的 IO 消耗完全相同,只是 在 CPU
方面,Offset and Fetch 方面要好一些,但是不明显。如果对于一个
每秒都要处理成千上万条的分页Sql语句的DB 来说,Offset and Fetch 在CPU
方面的优势会比较明显的,否则,性能的提升并不明显。 语法方面 Offset and
Fetch 则是十分的简洁,一句搞定,比起 Row_Number() 好了太多 ~ 同是
Offset and Fetch 并不仅仅可以用来分页哦,具体其他使用,大家可以自行参考
MSDN

有时候我们需要知道一段SQL语句执行的时间有多长。下面是其中一种办法。