Large data paging stored procedure efficiency test with test code and results

Test environment
Hardware: CPU core dual-core T5750 Memory: 2G
Software: Windows Server 2003 + SQL Server 2005
OK, we first create a database: Data_test, and create a table in this database: tb_testtable
Copy CodeThe code is as follows:
Create DATABASE Data_test--Creating databases
Data_test
Go
Use Data_test
Go
CREATE TABLE Tb_testtable--Creating tables
(ID int identity (1,1) primary key,
UserName nvarchar is not NULL,
Userpwd nvarchar is not NULL,
UserEmail nvarchar () null)
Go

Then we insert 2 million data into the datasheet:

Copy CodeThe code is as follows:
--Inserting data
SET Identity_insert tb_testtable on
DECLARE @count int
Set @count =1
While @count <=2000000
Begin
Insert into tb_testtable (Id,username,userpwd,useremail) VALUES (@count, ' admin ', ' admin888 ', ' lli0077@yahoo.com.cn ')
Set @count = @count +1
End
SET Identity_insert tb_testtable off

I first wrote five common stored procedures:
1, using select top and select Not in for paging, the specific code is as follows:
Copy CodeThe code is as follows: CREATE PROCEDURE Proc_paged_with_notin--using the Select top and select No in
(
@pageIndex int,--page index
@pageSize INT--Number of records per page
)
As
Begin
SET NOCOUNT on;
DECLARE @timediff datetime--time consuming
declare @sql nvarchar (500)
Select @timediff =getdate ()
Set @sql = ' Select Top ' +str (@pageSize) + ' * from tb_testtable where (ID No in (select Top ' +str (@pageSize * @pageIndex) + ' ID fr Om tb_testtable ORDER BY ID ASC)-ORDER by ID '
Execute (@sql)--a string @sql is written because it is not directly connected to the parameter after select top.
Select DateDiff (MS, @timediff, GetDate ()) as time consuming
SET NOCOUNT off;
End

2, using select top and select MAX (column key)
Copy CodeThe code is as follows: CREATE PROCEDURE Proc_paged_with_selectmax--using the Select top and select Max (column)
(
@pageIndex int,--page index
@pageSize INT--Number of page records
)
As
Begin
SET NOCOUNT on;
DECLARE @timediff datetime
declare @sql nvarchar (500)
Select @timediff =getdate ()
Set @sql = ' Select Top ' +str (@pageSize) + ' * tb_testtable where id> (select Max (ID) from (select Top ' +str (@pageSize *@ PageIndex) + ' ID from tb_testtable-id ' as temptable) ' ORDER by ID '
Execute (@sql)
Select DateDiff (MS, @timediff, GetDate ()) as time consuming
SET NOCOUNT off;
End

3, using select top and intermediate variables--this method is best for someone on the web, so stick it together and test it.
Copy CodeThe code is as follows: CREATE PROCEDURE Proc_paged_with_midvar--using id> Maximum ID value and intermediate variable
(
@pageIndex int,
@pageSize int
)
As
DECLARE @count int
DECLARE @ID int
DECLARE @timediff datetime
declare @sql nvarchar (500)
Begin
SET NOCOUNT on;
Select @count =0, @ID =0, @timediff =getdate ()
Select @count = @count +1, @ID =case when @count <= @pageSize * @pageIndex then ID else @ID the end from tb_testtable the order by ID
Set @sql = ' Select Top ' +str (@pageSize) + ' tb_testtable where id> ' +str (@ID)
Execute (@sql)
Select DateDiff (MS, @timediff, GETDATE ()) as time consuming
SET NOCOUNT off;
End

4, using the Row_number () method for the new method in SQL Server 2005, using Row_number () to index data rows
Copy CodeThe code is as follows: CREATE PROCEDURE Proc_paged_with_rownumber--using the Row_number in SQL 2005 ()
(
@pageIndex int,
@pageSize int
)
As
DECLARE @timediff datetime
Begin
SET NOCOUNT on;
Select @timediff =getdate ()
SELECT * FROM (select *,row_number ()-Idrank from tb_testtable) as Idwithrownumber where Idrank&gt @pageSize * @pageIndex and idrank< @pageSize * (@pageIndex + 1)
Select DateDiff (MS, @timediff, GETDATE ()) as time consuming
SET NOCOUNT off;
End

5, using temporary tables and row_number
Copy CodeThe code is as follows: CREATE PROCEDURE proc_cte--using temporary tables and row_number
(
@pageIndex int,--page index
@pageSize INT--Number of page records
)
As
SET NOCOUNT on;
declare @ctestr nvarchar (400)
declare @strSql nvarchar (400)
DECLARE @datediff datetime
Begin
Select @datediff =getdate ()
Set @ctestr = ' with Table_cte as
(Select Ceiling (Row_number () () over ()/' +str (@pageSize) + ') as page_num,* from Tb_testtable) ';
Set @strSql = @ctestr + ' select * from table_cte where page_num= ' +str (@pageIndex)
End
Begin
Execute sp_executesql @strSql
Select DateDiff (MS, @datediff, GetDate ())
SET NOCOUNT off;
End

OK, now that the stored procedure has been created, we are on page 2nd, page 1000th, 10,000th, 100,000th, 199,999th page with 10 data per sheet, and the time consuming unit: MS per page Test 5 times to take its average
Save 2nd Page Time consuming 1000th page Time 10,000th page time consuming 100,000th page time consuming 199,999th page time consuming efficiency ranking
1 with not in 0ms 16ms 47ms 475ms 953ms 3
2 with select Max 5ms 16ms 35ms 325ms 623ms 1
3 middle variable 966ms 970ms 960ms 945ms 933ms 5
4row_number 0ms 0ms 34ms 365ms 710ms 2
4 temporary table 780ms 796ms 798ms 780ms 805ms 4

Test results show: select Max >row_number>not in> temporary table > Intermediate variables
So I extended the most efficient select Max method with the 2-point code from the Internet, and I modified the ASC sort without a value for the bug, test results:
2-minute 156ms 156ms 180ms 470ms 156ms 1*
Judging from the test results, using the 2-point method can improve efficiency and make the efficiency more stable, I added the 159,999th page of the test, only 296ms, the effect is quite good!
Here's a 2-point code using Select Max, which is pretty well established.

Copy CodeThe code is as follows:
--/*-----Stored Procedure paging Sun Wei 2005-03-28 create-------* *
--/*-----Stored Procedures Paging process dust 2008-9-1 modified----------* *
--/*-----2-point data processing so that the first half of the query data and query the second half of the data performance of the same-------* *

Alter PROCEDURE Proc_paged_2part_selectmax
(
@tblName nvarchar,----a connection to a table or tables to display
@fldName nvarchar = ' * ',----list of fields to display
@pageSize int = Ten,----the number of records displayed per page
@page int = 1,----to display the record for that page
@fldSort nvarchar = null,----sort field list or condition
@Sort bit = 0,----Sort method, 0 is ascending, 1 is descending (if a multiple-field arrangement sort refers to the order of the last sorted field (the last sort field is not ordered)--Program reference: ' Sorta ASC,SORTB desc,sortc '
@strCondition nvarchar (1000) = NULL,----query criteria, no where
@ID nvarchar,----primary key for the primary table
@Dist bit = 0,----whether to add a query field DISTINCT default 0 does not add/1 add
@pageCount int = 1 OUTPUT,----Total number of pages after the query results are paginated
@Counts int = 1 output----query to the number of records
)
As
SET NOCOUNT on
Declare @sqlTmp nvarchar (1000)----store dynamically generated SQL statements
Declare @strTmp nvarchar (1000)----A query that holds the total number of query results
Declare @strID nvarchar (1000)----A query that holds the ID at the beginning or end of a query

Declare @strSortType nvarchar (a)----data collation a
Declare @strFSortType nvarchar (a)----data collation B

Declare @SqlSelect nvarchar----SQL construction of queries containing distinct
Declare @SqlCounts nvarchar----SQL construction of total queries containing distinct

DECLARE @timediff datetime-time-consuming test time lag
Select @timediff =getdate ()

If @Dist = 0
Begin
Set @SqlSelect = ' SELECT '
Set @SqlCounts = ' Count (*) '
End
Else
Begin
Set @SqlSelect = ' SELECT distinct '
Set @SqlCounts = ' Count (DISTINCT ' + @ID + ') '
End


If @Sort =0
Begin
Set @strFSortType = ' ASC '
Set @strSortType = ' DESC '
End
Else
Begin
Set @strFSortType = ' DESC '
Set @strSortType = ' ASC '
End



--------Generate query Statements--------
--Here @strtmp statements to get the number of query results
If @strCondition is null or @strCondition = '--no display condition set
Begin
Set @sqlTmp = @fldName + ' from ' + @tblName
Set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName
Set @strID = ' from ' + @tblName
End
Else
Begin
Set @sqlTmp = + @fldName + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
Set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
Set @strID = ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
End

----The total number of results obtained from the query-----
EXEC sp_executesql @strTmp, N ' @Counts int out ', @Counts out
DECLARE @tmpCounts int
If @Counts = 0
Set @tmpCounts = 1
Else
Set @tmpCounts = @Counts

--Get Total paging
Set @pageCount = (@tmpCounts + @pageSize-1)/@pageSize

/**//**//**//** the current page is greater than the total number of pages to take the last page **/
If @page > @pageCount
Set @page = @pageCount

--/*-----Data Paging 2-------* *
DECLARE @pageIndex INT--Total/page size
DECLARE @lastcount INT--Total% page size

Set @pageIndex = @tmpCounts/@pageSize
Set @lastcount = @tmpCounts% @pageSize
If @lastcount > 0
Set @pageIndex = @pageIndex + 1
Else
Set @lastcount = @pagesize

--//*** Display Page Paging
If @strCondition is null or @strCondition = '--no display condition set
Begin
If @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
Begin
If @page =1
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
Else
Begin
If @Sort =1
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
End
Else
Begin
Set @page = @pageIndex-@page +1--the latter half of data processing
If @page <= 1--last page data display
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@lastcount as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
Else
If @Sort =1
Begin
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
End

Else--There are query criteria
Begin
If @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
Begin
If @page =1
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where 1=1 ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
else if (@Sort =1)
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
Else
Begin
Set @page = @pageIndex-@page +1--the latter half of data processing
If @page <= 1--last page data display
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@lastcount as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @str Fsorttype
else if (@Sort =1)
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
Else
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End

------Return Query Results-----
EXEC sp_executesql @strTmp
Select DateDiff (MS, @timediff, GETDATE ()) as time consuming
--print @strTmp
SET NOCOUNT off
Go

Executive example: Exec proc_paged_2part_selectmax ' tb_testtable ', ' id,username,userpwd,useremail ', 10,100000, ' id ', 0,null, ' id ', 0
This test only on the stand-alone, and not in the actual development of Web projects in the paging test, the test item is also relatively single, so not comprehensive system, but in terms of its efficiency, we can in the database paging algorithm for effective control.