Sql Server 2012 的新分页方法分析(offset and fetch) - 转载

时间:2021-12-22 06:49:28

最近在分析 Sql Server 2012 中 offset and fetch 的新特性,发现 offset and fetch 无论语法的简洁还是功能的强大,都是相当相当不错的

其中 offset and fetch 最重要的新特性是 用来 分页,既然要分析 分页,就肯定要和之前的分页方式来比较了,特别是 Row_Number() 了,在比较过程中,发现了蛮多,不过最重要的,通过比较本质,得出了优劣,也和大家一起分享下。
准备工作,建立测试表:Article_Detail,主要是用来存放一些文章信息,测试的时间,都是从网易上面转载的新闻,同时,测试表数据字段类型是比较均匀的,为了更好的测试,表结构如下图:

Sql Server 2012 的新分页方法分析(offset and fetch)  - 转载

表数据:

Sql Server 2012 的新分页方法分析(offset and fetch)  - 转载

数据量: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 ( [ 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 的分页方法 (从Sql Server 2012开始支持该语法)
语法:
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 --经过测试offset and fetch语法必须跟在order by后面用,否则会报语法错误,也就是说offset and fetch语法和row_number函数一样,需要用到一列数据来排序
set statistics profile off;

一句就搞定!

性能比较
1. NTILE() 的执行计划

Sql Server 2012 的新分页方法分析(offset and fetch)  - 转载

从执行计划中,就可以看出来,进行了一次全表扫表,两次 Nested Loops ,还有无数其他运算,就一次全表扫表,就知道性能之差了

2. ROW_NUMBER() 的执行计划

Sql Server 2012 的新分页方法分析(offset and fetch)  - 转载

从执行计划中可以看出来, 聚集索引扫描占用了100% 的资源,但是通过 EstimateRows = 100 和 Rows = 750 可以看出来,并没有进行全表扫描,并且IO 操作很小,所以性能还是很不错的

3. Offset and Fetch 的 执行计划

Sql Server 2012 的新分页方法分析(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 Server 2012 的新分页方法分析(offset and fetch) - 转载的更多相关文章

  1. Sql Server 2012 分页方法分析&lpar;offset and fetch&rpar;

    最近在分析 Sql Server 2012 中 offset and fetch 的新特性,发现 offset and fetch 无论语法的简洁还是功能的强大,都是相当相当不错的.其中  offse ...

  2. 【数据库】 SQL SERVER 2012 实用新特性

    [数据库] SQL SERVER 2012 实用新特性 官方链接 一. ALWAYS ON - 灾难恢复 二. 列存储索引 - 比非聚集索引效率高,但有索引表不允许修改数据(插入,更新,删除),用于读 ...

  3. SQL Server 2012 T-SQL 新特性

    序列 Sequence SQL Server 现在将序列当成一个对象来实现,创建一个序列的例子语法如下: CREATE SEQUENCE DemoSequence START WITH 1 INCRE ...

  4. SQL Server 2012 管理新特性:AlwaysOn 可用性组

    SQL Server 2012 新特性(一)管理新特性:AlwaysOn 一.准备环境 1.准备4台计算机 域控制器DC1,IP地址192.168.1.1 主节点SQL1:IP地址192.168.1. ...

  5. SQL Server 2012中LEAD函数简单分析

    LEAD函数简单点说,就是把下一行的某列数据提取到当前行来显示,看示例更能解释清楚,先看测试用脚本 DECLARE @TestData TABLE( ID INT IDENTITY(1,1), Dep ...

  6. sql server 2012使用新特性offset和fetch next完成分页操作

    1 select * from HumanResources.Department order by DepartmentID offset rows fetch next rows only; of ...

  7. SQL Server 2012 开发新功能 序列对象(Sequence)(转)

    转载链接:http://www.cnblogs.com/zhangyoushugz/archive/2012/11/09/2762720.html 众所周知,在之前的sqlserver版本中,一般采用 ...

  8. SQL Server 2012 管理新特性:AlwaysOn【转】

    http://jimshu.blog.51cto.com/3171847/871169 见超链接

  9. SQL Server的三种分页方式

    直接上代码 --top not in方式 select top 条数 * from tablename where Id not in (select top 条数*页数 Id from tablen ...

随机推荐

  1. 自己动手编写spring IOC源码

    前言:对于spring IOC概念不是很了解的朋友可以阅读我上一篇博客--轻松理解spring IOC(这两篇博客也是由于我的个人原因导致现在才发布,惭愧啊).通过这篇博客的理解之后,相信大家会对sp ...

  2. 9&period; javacript高级程序设计-客户端检测

    1. 客户端检测 1.1 能力检测 在编写代码之前先检测特定浏览器的能力. 1.2 怪癖检测 怪癖实际上是浏览器实现中的bug 1.3 用户代理检测 通过检测用户代理字符串来识别浏览器.用户代理字符串 ...

  3. 阿里云服务器 &amp&semi;&amp&semi; 如何window链接到阿里云服务器

    现在的时间是:2016年10月11日 1:购买学生机 阿里云手机app上  ->  学生专区  ->  购买: 需要注意的是:如果没有自己需要的系统,比如没有linux操作系统的ecs,那 ...

  4. 《A First Course in Probability》-chaper7-期望的性质-相关系数

    之前我们介绍过,协方差能够一定程度上描述两个变量之间的相关性,但是有时候它并没有那么准确,例如下面这个例子: 本质一样的两个随机变量,独立性是不变的,但是通过这个等式我看到,如果在随机变量的前面添加了 ...

  5. 嵌入式OS入门笔记-以RTX为案例:六&period;RTX的任务调度

    嵌入式OS入门笔记-以RTX为案例:六.RTX的任务调度 上一篇笔记介绍了一些绕开排程器(或调度程序,scheduler)来进行时间管理的一些小方法.这一篇详细介绍RTX的任务调度原理. RTX主要有 ...

  6. Golang学习--包管理工具glide

    上一篇文章中我们已经成功的运行了go的代码,这是我们迈出的最基础的一步. 一个项目通常会依赖很多外部的库,当依赖的库比较多的时候,手工管理就会比较麻烦,这个时候就需要包管理工具出场了,帮你管理好所有依 ...

  7. springboot2&plus;freemarker简单使用

    一.src/main/resources/templates下新建welcome.ftl <!DOCTYPE html> <html lang="en"> ...

  8. Python复习笔记(六)网络编程&lpar;udp&sol;tcp&rpar;

    一.网络-udp(用户数据报协议) 用户数据报协议 类似写信,不安全,数据有可能丢 1.1 ip地址 注意: IP地址127.0.0.1 ~ 127.255.255.255 用于回路测试 私有ip地址 ...

  9. CentOS7下启用网卡

    [root@localhost network-scripts]# ls ifcfg-ens99 ifdown-ippp ifdown-sit ifup-bnep ifup-plip ifup-Tea ...

  10. ios实现分发下载

    背景:原来公司Jenkins打包后的ipa和apk都是通过第三方的平台托管,手动上传,然后去扫二维码下载.虽然第三方平台有Jenkins插件来直接上传到该平台,但是想自己进行管理.所以就自己来做安装包 ...