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
withcte_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 * Fromcte_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
Pages |
TopN |
Cte |
Temporary tables |
Temporary tables |
Old Forum Stored procedures |
CTE Improvements |
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 |
Cannot calculate |
9806 |
869 |
2578 |
635 |
8948 |
3162 |
Cannot calculate |
9822 |
2485 |
4110 |
12460 |
8210 |
10000 |
Cannot calculate |
9754 |
7812 |
11926 |
14250 |
7359 |
31623 |
Cannot calculate |
9775 |
18729 |
33218 |
15249 |
7511 |
100000 |
Cannot calculate |
Cannot calculate |
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