One of the most important new features of the offset and fetch is the paging, since to analyze pagination, it is certainly to and before the paging way to compare, especially row_number (), in the comparison process, found quite a lot, but the most important, through the comparison of nature, to get the pros and cons, and share it with all of you.
Prepare work, set up Test table: Article_detail, mainly used to store some article information, testing time, are reproduced from above NetEase news, at the same time, test table data field type is more uniform, in order to better test, table structure as follows:
Data Volume: 129,991 Records
Grammar Analysis
1. Ntile () Paging method
The Ntile () method can be used for paging, however, the application scene is very narrow, and poor performance, and row_number () and offset fetch paging is not any advantage, and only in the read-only table pagination, or more appropriate; Although not useful, but also to pagination, So just a simple introduction.
Grammar:
Ntile (integer_expression) over ([<partition_by_clause>] < Order_by_clause >)
Distributes 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 to be used in the test:
Copy Code code as follows:
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 of the above figures are calculated on the basis of rowcount/pagesize, but the introduction can be made by reference to the MSDN
2. Row_number () Paging method
In the version after SQL Server 2000, row_number () This paging is always good, much better than the previous cursor paging, because row_number () does not cause a full table sweep, but the syntax is more complex, and, as page numbers increase, Performance is also getting worse.
Grammar:
Row_number () over ([PARTITION by Value_expression, ... [n]] order_by_clause)
SQL statements to be used in the test:
Copy Code code as follows:
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 (50-1) +1) and 15 * 50
Set STATISTICS profile off;
3. Paging method for Offset and Fetch
Grammar:
OFFSET {integer_constant | offset_row_count_expression} {ROW | ROWS}
FETCH {a | NEXT} {integer_constant | fetch_row_count_expression} {ROW | ROWS} only
From the syntax you can see that two methods can not only connect the Intege type of parameters, but also to the expression, such as 1*2 +3, while, Row or Rows are case-insensitive and single plural oh
Looking at the test of the SQL statement, is really concise can not be more concise, see two times can remember the syntax, pagination can be so concise:
Copy Code code as follows:
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 (50-1) ROW FETCH NEXT rows
Set STATISTICS profile off;
One sentence will be done!
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, on 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 you can see by estimaterows = 100 and Rows = 750 that there is no full table scan and that the IO operation is small, so the performance is pretty good
3. Execution plan for Offset and Fetch
The execution plan has only 3 lines 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 reduced CPU consumption 。
Comprehensive comparison:
In SQL Server 2012, in a paging method, the Offset and Fetch has an advantage over row_number (), both in terms of performance and syntax.
But the performance aspect, the advantage is not too big, both IO consumption is identical, only in the CPU aspect, the Offset and Fetch aspect is better, but is not obvious. If you have a db that handles thousands of paging SQL statements per second, the advantages of Offset and Fetch are more obvious in terms of CPU, otherwise, performance is not significantly improved.
The syntax of Offset and Fetch is very concise, a good sentence, compared to row_number () too much ~
The same is Offset and Fetch not only can be used for paging oh, specifically other uses, you can refer to the MSDN
