New paging method Analysis for SQL Server 2012 (offset and fetch)-reproduced

Recently in the analysis of the new features of offset and fetch in SQL Server 2012, it is quite good to find that the offset and fetch, whether the syntax is concise or powerful, are fairly

Which offset and fetch the most important new features are used for paging, since to analyze the paging, it must be compared with the previous pagination, especially row_number (), in the comparison process, found quite a lot, but the most important, through the comparison of nature, to obtain the merits and demerits, and share it with everyone.
Prepare the work, establish the test table: Article_detail, mainly used to store some information, testing time, are reproduced from NetEase above the news, at the same time, the Test table data field type is more uniform, in order to better test, table structure such as:

Table data:

Data Volume: 129,991 Records

Syntax analysis

1. Pagination method for NTILE ()

The NTILE () method can be used for paging, but the application scenario is very narrow, and the performance is poor, and row_number () and offset fetch paging is not any advantage, and only in the read-only table above the page, it is more appropriate, although not useful, but also can be paged, So just a brief introduction.

Grammar:

NTILE (integer_expression) over ([<partition_by_clause>] < Order_by_clause >) distributes the rows in an ordered partition to a specified number of groups. Each group has a number, numbering from the beginning. For each row, NTILE returns the number of the group to which this row belongs.

SQL statements used in the test:

 
 
1 set statistics time on 
2 set statistics io on 
3 set statistics profile on; 
4 with #pager as 
5 ( 
6 select ID,Title,NTILE(8666) OVER(Order By ID) as pageid from Article_Detail 
7 ) 
8 select ID,Title from #pager where pageid=50 
9 set statistics profile on;

8666 of the above figure is calculated according to Rowcount/pagesize, but the introduction, you can refer to the MSDN

2. Pagination method for Row_number ()

In the later versions of SQL Server 2000, Row_number () has been a good paging method, with a much better performance than the previous cursor pagination because row_number () does not cause a full table sweep, but the syntax is complex and, as the page numbers increase, Performance is also getting worse. Syntax: Row_number () over ([PARTITION by Value_expression, ... [n]] order_by_clause) The SQL statement used in the test:

 
 1 dbcc freeproccache 
 2 dbcc dropcleanbuffers 
 3 set statistics time on 
 4 set statistics io on 
 5 set statistics profile on; 
 6 with #pager as 
 7 ( 
 8 select ID,Title,ROW_NUMBER() OVER(Order By ID) as rowid from Article_Detail 
 9 ) 
10 select ID,Title from #pager where rowid between (15 * (50-1)+1) and 15 * 50 
11 set statistics profile off;

3. The paging method for Offset and Fetch (this syntax is supported starting with SQL Server 2012)
Grammar:
OFFSET {integer_constant | offset_row_count_expression} {ROW | ROWS}
FETCH {First | NEXT} {integer_constant | fetch_row_count_expression} {ROW | ROWS} only
From the syntax can be seen in the following two methods can not only take intege type parameters, but also can receive expressions, such as 1*2 +3, at the same time, Row or Rows are not case-sensitive and single-plural oh
Look at the test SQL statement, is really concise can not be concise, see two times can remember the syntax, pagination can be so concise:

 
1 dbcc freeproccache 
2 dbcc dropcleanbuffers 
3 set statistics time on 
4 set statistics io on 
5 set statistics profile on; 
6 select ID,Title from Article_Detail order by id OFFSET (15 * (50-1)) ROW FETCH NEXT 15 rows only 
7 set statistics profile off;

Just one sentence!

Performance comparison
1. Implementation plan for NTILE ()

From the execution plan, it can be seen that a full table sweep table, two times Nested Loops, there are countless other operations, a full table sweep table, you know the difference in performance

2. Implementation plan for Row_number ()

As you can see from the execution plan, the clustered index scan takes up 100% of the resources, but it can be seen through estimaterows = 100 and Rows = 750, not full-table scans, and the IO operation is small, so performance is good.

3. Execution plan for Offset and Fetch

The execution plan is only 3 rows and consumes 100% of the IO operations, estimaterows = 100 and Rows = 750 are exactly the same as row_number (), but others are much less, that is, there is no full table scan and lower CPU consumption 。

Comprehensive Comparison:
In SQL Server 2012, in the paging method, Offset and Fetch compares with Row_number (), which is advantageous in both performance and syntax.

But in terms of performance, the advantage is not too large, the IO consumption is exactly the same, but on the CPU side, Offset and Fetch better, but not obvious. If the CPU advantage of Offset and Fetch is obvious for a db that handles thousands of paging SQL statements per second, the performance improvement is not obvious.

The grammatical aspect of Offset and Fetch is very concise, a sentence to be done, compared to row_number () good too much ~
The same is Offset and Fetch not only can be used for paging Oh, specific other use, you can refer to MSDN

New paging method Analysis for SQL Server 2012 (offset and fetch)-reproduced