[Reading experience] optimization of data paging, taking offset-fetch of SQL 2012 as an example

This is my article backup, original source: [reading experience] data paging optimization, with SQL 2012 Offset-fetch as an example

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

This article originates from Microsoft Press

The 2015 new book- t-SQL querying

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

The Microsoft website has published the full text of This chapter , please see

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

The second page describes the database paging SQL instructions,

Includes TOP,row_number(available as SQL 2005),offset-fetch(available as SQL 2012)

Because our company has been changed to SQL 2012, so I'm only extracting part of Offset-fetch.

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

SQL has been more than offset-fetch the practice,

It's simpler and stronger than the top, row_number.

Below is the general approach (not yet optimized), similar to the example in the previous chapters:

--Annotations: Improved stored procedures (not yet optimized).

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

--Annotation: Executes this stored procedure.

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

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

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

is a simple commentary that lets you understand the use of Offset-fectch

We can further revise the following to make the search more effective.

According to a Microsoft-published book test, when you read the data on page 1000th, the following notation requires only 241 logical reads,

The above-mentioned wording is 76,644 times, the efficiency has greatly improved.

--Annotations: Improved stored procedures (optimization).

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

--Annotation: Executes this stored procedure.

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

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

If you want to learn more about the principles inside

Just look at the Microsoft public Chapter,

A total of six pages, written very detailed!

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

This site has published the relevant articles--

Last episode Ch 14-4 compose the DataReader of the page # # (with sql command row_number) last episode ch 14-4 to write the paging program of ADO-DataReader (with SQL 2012 instruction OFFSET ... FETCH)

[ASP. 4.5/vs 2012] GridView Custom Paging new properties, AllowCustomPaging and Virtualitemcount[.net 4.5]gridview new properties for custom paging, allowcustompaging and VirtualItemCount #2 Example-DataReader + database Paging

Model binding Primer, Introduction, initial skill #2-Web form paging and IQueryable (not using EF)

This is my article backup, original source: [reading experience] data paging optimization, with SQL 2012 Offset-fetch as an example

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

[reading experience] optimization of data paging, as an example of SQL 2012 Offset-fetch