Performance comparison of paging implementation methods

We first give a few main pagination methods and core statements, and then give the conclusion directly, interested readers can look at the following data

Several methods of paging in common stored procedures

TopN method

Select Top (@PageSize) from TableName where ID is not in

(Select Top ((@PageIndex-1) * @PageSize) ID from Table Name where ...)

Where .... ...

Temporary tables

Declare @indextable table (ID int identity (1,1), nid int,postusername nvarchar (50))

DECLARE @PageLowerBound int

DECLARE @PageUpperBound int

Set @PageLowerBound = (@pageindex-1) * @pagesize--Lower bound

Set @PageUpperBound = @PageLowerBound + @pagesize--upper bound

SET ROWCOUNT @PageUpperBound

Insert into @indextable (nid,postusername) Select Replyid,postusername from TableName ...

SELECT * FROM TableName p, @indextable t where P.id=t.nid

and t.id> @PageLowerBound and t.id<= @PageUpperBound ORDER by t.id

cte--2005 new syntax, similar to temporary table, but the life cycle is slightly different, here is only one of his application

With cte_temp--defines a zero table, pageindex is a calculated field that stores the page number of the search results

As (Ceiling () (Row_number () over () -1)/@pagesize as int) as pageindex,* from TableName where ...)
SELECT * from Cte_temp where pageindex= @pageindex-1;

Conclusion:

TOPN is the fastest in a small number of pages, if it is below 10 page, you can consider it, CTE and temporary table time is very stable, CTE consumes more time than the temporary table, but does not cause the growth of tempdb and IO increase

Performance comparison

Test environment: win2003server,sqlserver2005, library size 2,567,245 lines, there is no WHERE clause, the trial time per page size 50, page number as a variable

Take 0,3,10,31,100,316,1000,3162 ... page, which is 10 index, the test results are as follows

Number of pages topn CTE temporary table (with cache) temporary table (no cache)
The stored procedure CTE improvements that the company is using
1 3 12 10 101 457 7302
3 15 7 79 5524 464 7191
10 127 5504 88 3801 464 6116
32 588 9672 122 3601 976 7602
100 4680 9738 166 4235 486 7151
316 45271 9764 323 3867 522 7255
1000 Null 9806 869 2578 635 8948
3162 Null 9822 2485 4110 12460 8210
10000 Null 9754 7812 11926 14250 7359
31623 Null 9775 18729 33218 15249 7511
100000 null 31538 55569 17139 6124

Data Interpretation and analysis

A temporary table is divided into two kinds of time, the CTE is the above method, the CTE improvement simply reduces the number of columns selected in the CTE temporary table, selects only the page number and primary key, and Null indicates that the time cannot be computed (the time is too long) and the unit of data is milliseconds.

As you can see from the above data, TOPN has an advantage on the first 32 pages, but when the number of pages increases, the performance is reduced very quickly, the CTE improvement is better than the CTE, the average progress is about two seconds, but it is slower than the temporary table, but considering the temporary table will increase the size of the log file, resulting in a lot A CTE has its own advantages, and the stored procedures that the company is using are efficient, but the performance degrades when the page is back