We first give several main paging methods and core statements, and then directly give the conclusion. Interested readers can take a look at the following data for several commonly used stored procedure paging methods.
TopN method
select Top(@PageSize) from TableName where ID Not IN
(Select Top ((@PageIndex-1)*@PageSize) ID from Table Name where .... order by ... )
where .... order by ...
temporary table
Copy the code code as follows:
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 limit
set @PageUpperBound=@PageLowerBound+@pagesize--upper limit
set rowcount @PageUpperBound
insert into @indextable(nid,PostUserName) select ReplyID,PostUserName from TableName order by ......
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 tables, but the life cycle is slightly different. Here is just an application of it.
with cte_temp--Define the zero-time table. PageIndex is a calculated field that stores the page number of the search results.
As (ceiling((Row_Number() over(order by .... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)
select * from cte_temp where pageindex=@pageindex-1;
in conclusion:
TopN is the fastest when the number of pages is small. If it is less than 10 pages, you can consider using it. The CTE and temporary table times are very stable. CTE consumes more time than the temporary table, but it will not cause tempdb to skyrocket and IO to increase.
Performance comparison
Test environment: win2003server, Sqlserver2005, library size 2,567,245 rows, no where clause, page size 50 during test, page number as variable
Take 0, 3, 10, 31, 100, 316, 1000, 3162... pages, which is the index of 10. The test results are as follows
Number of pagesTopN CTE Temporary table (with cache) Temporary table (without cache)
Stored procedure CTE improvements 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 Null 31538 55569 17139 6124
Data interpretation and analysis
Temporary tables are divided into two types of time: cache or not. CTE is the above method. The CTE improvement only reduces the number of columns selected into the CTE temporary table. Only the page number and primary key are selected. Null means that the time cannot be calculated (the time is too long) , the data unit is milliseconds.
From the above data, we can see that TopN has advantages in the first 32 pages, but as the number of pages increases, the performance decreases quickly. The CTE improvement is better than the CTE, with an average improvement of about two seconds, but it is still better than the temporary table. Slow, but considering that the temporary table will increase the size of the log file and cause a lot of IO, CTE also has its own advantages. The stored procedure currently used by the company is very efficient, but the performance will decrease when the page number is later.