MSSqlServer 2012 offset and fetch paging method analysis ()

Prepare a test table: Article_Detail, which is mainly used to store some article information. The test time is the news reposted on NetEase. At the same time, the data field types in the Test table are relatively uniform, for better testing, the table structure is shown in the following figure:

 

 

 

Content:

 

 

 

Data Volume: 129,991 records

 

 

Syntax analysis

 

1. NTILE () paging method

 

The NTILE () method can be used for paging, but the application scenario is very narrow and has poor performance. It has no advantage over Row_Number () and offset fetch paging, it is more appropriate to paging the read-only table. Although it is not easy to use, it can still be paging, so it is only a brief introduction.

 

Syntax:

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

 

Distribute rows in an ordered partition to a specified number of groups. Each group is numbered from the beginning. For each row, NTILE returns the group ID of the row.

 

SQL statements used in the test:

SQL statements used in the test:
 

The code is as follows:	Copy code
Set statistics time on Set statistics io on Set statistics profile on;   With # pager ( 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;
Press Ctrl + C to copy the code
The 8666 value in the preceding figure is calculated based on RowCount/Pagesize. For more information, see
 
2. ROW_NUMBER () paging method
 
In versions later than SQL Server 2000, the ROW_NUMBER () paging method has always been very good. Compared with the previous cursor paging method, the performance is much better, because ROW_NUMBER () it does not cause a full table scan. However, the syntax is complex and the performance is getting worse as the page number increases.
 
Syntax:
ROW_NUMBER () OVER ([partition by value_expression,... [n] order_by_clause)
SQL statements used in the test:
 

The code is as follows:	Copy code
Dbcc freeproccache Dbcc dropcleanbuffers Set statistics time on Set statistics io on Set statistics profile on;   With # pager ( 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. Paging method of Offset and Fetch

 
Syntax:
 
OFFSET {integer_constant | offset_row_count_expression} {ROW | ROWS}
 
FETCH {FIRST | NEXT} {integer_constant | fetch_row_count_expression} {ROW | ROWS} ONLY
From the syntax, we can see that two methods can be followed by parameters of the intege type and expressions, such as 1*2 + 3. At the same time, row or Rows are case-insensitive and case-insensitive.
 
The SQL statements used for testing are really concise and cannot be concise. The syntax that can be remembered twice can be so concise:
 

The code is as follows:	Copy code
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   Set statistics profile off;

That's all you need to do!
 
Performance Comparison
 
1. NTILE () execution plan

 

From the execution plan, we can see that we performed a full table scan, two Nested Loops, and countless other operations to scan the table at a time, the performance is poor.

 

 

2. ROW_NUMBER () execution plan

 

 

According to the execution plan, clustered index scanning occupies 100% of the resources, but we can see from EstimateRows = 100 and Rows = 750 that no full table scan is performed, and IO operations are very small, so the performance is still very good

 

 

3. Execution plan of Offset and Fetch

 

 

 

The execution plan has only three Rows and occupies 100% of the resource I/O operations. EstimateRows = 100 and Rows = 750 are exactly the same as ROW_NUMBER (), but some other operations are much less, that is to say, there is no full table scan and the CPU consumption is reduced.

 

 

Comprehensive comparison:

 

In SQL Server 2012, in the paging method, Offset and Fetch are advantageous compared with ROW_NUMBER () in terms of performance and syntax.

 

However, in terms of performance, the advantage is not too great. The IO consumption of the two is the same, but in terms of CPU, Offset and Fetch are better, but not obvious. For a DB that processes thousands of paging SQL statements per second, the advantages of Offset and Fetch in CPU are obvious. Otherwise, the performance improvement is not obvious.

 

In terms of syntax, Offset and Fetch are very concise. It is much better than Row_Number ~