[读书心得]资料分页的优化，以SQL 2012的 OFFSET-FETCH为例

这是我的文章备份，原始出处：[读书心得]资料分页的优化，以SQL 2012的 OFFSET-FETCH为例

http://www.dotblogs.com.tw/mis2000lab/archive/2015/04/10/sql_querying_paging_offset-fetch.aspx

这篇文章源自微软出版社（Microsoft Press）

2015年的新书 -- T-SQL Querying

• Published 3/6/2015
• 1st Edition
• 864 pages
• Book 978-0-7356-8504-8
• eBook 978-0-13-398664-8

微软网站已经公开了 这一章 的全文，请看

https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=1

第二页里面 介绍数据库分页的SQL指令，

包含 TOP、ROW_NUMBER（SQL 2005 起可用）、OFFSET-FETCH（SQL 2012 起可用）

因为我们公司都改成SQL 2012版了，所以我只摘录 OFFSET-FETCH的部分

====================================================================

SQL 2012起 多了OFFSET-FETCH的作法，

比起上述的TOP、ROW_NUMBER更简单而且更强。

底下是一般的作法（尚未优化），跟前面章节的范例雷同：

-- 批注：改良后的预存程序（尚未优化）。

CREATE PROC dbo.GetPage5

@pagenum  AS BIGINT = 1,

@pagesize  AS BIGINT = 25

AS

SELECT orderid, orderdate, custid, empid

FROM dbo.Orders

ORDER BY orderid

OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

GO

-- 批注：执行这一段预存程序。

EXEC dbo.GetPage5 @pagenum = 1, @pagesize = 25;

EXEC dbo.GetPage5 @pagenum = 2, @pagesize = 25;

EXEC dbo.GetPage5 @pagenum = 3, @pagesize = 25;

下图是简单的解说，让您了解 OFFSET-FECTCH的用法

我们可以进一步修正如下，让搜寻的效能更好。

根据微软出版书籍所做的测试，在您阅览第一千页的数据时，下面的写法只需逻辑读取241次，

而上述的写法却要76,644次，效能大幅提升了。

-- 批注：改良后的预存程序（优化）。

CREATE PROC dbo.GetPage6

  @pagenum  AS BIGINT = 1,

  @pagesize  AS BIGINT = 25

AS

WITH K AS   -- Define a table expression based on this query (call it K, for keys).

(

  SELECT orderid

  FROM dbo.Orders

  ORDER BY orderid

  OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY

)

SELECT O.orderid, O.orderdate, O.custid, O.empid

FROM dbo.Orders AS O

  INNER JOIN K

    ON O.orderid = K.orderid

ORDER BY O.orderid;

GO

-- 批注：执行这一段预存程序。

EXEC dbo.GetPage6 @pagenum = 3, @pagesize = 25;

====================================================================

如果您想进一步了解里面的原理

就直接看微软公开的这一章吧，

共有六页，写得很详尽！

https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=1

本站已发表的相关文章 --

上集 Ch 14-4 撰写ADO.NET DataReader的分页程序#1（搭配SQL指令 ROW_NUMBER）

上集 Ch 14-4 撰写ADO.NET DataReader的分页程序#3（搭配SQL 2012指令 OFFSET...FETCH）

[ASP.NET 4.5 / VS 2012]GridView自定义分页的新属性，AllowCustomPaging与 VirtualItemCount

[.NET 4.5]GridView自定义分页的新属性，AllowCustomPaging与 VirtualItemCount #2 范例 - DataReader +数据库分页

Model Binding入门、简介、初试身手 #2 -- Web Form分页与 IQueryable (不使用EF)

这是我的文章备份，原始出处：[读书心得]资料分页的优化，以SQL 2012的 OFFSET-FETCH为例

http://www.dotblogs.com.tw/mis2000lab/archive/2015/04/10/sql_querying_paging_offset-fetch.aspx