After testing, when querying page 100000 among 14483461 records, with 10 records per page in ascending and descending order, the first time is 0.47 seconds, and the second time is 0.43 seconds. The test syntax is as follows: powered by vevb.com
exec GetRecordFromPage news,newsid,10,100000
news is the table name, newsid is the key field, please index newsid first when using it.
Copy the code code as follows:
/*
Function name: GetRecordFromPage
Function: Get the data of the specified page
Parameter description: @tblName table name containing data
@fldName key field name
@PageSize Number of records per page
@PageIndex The page number to be obtained
@OrderType sorting type, 0 - ascending order, 1 - descending order
@strWhere query conditions (note: do not add where)
Author: Iron Fist
Email: [email protected]
Creation time: 2006-07-04
Modification time: 2006-07-04
*/
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- table name
@fldName varchar(255), -- field name
@PageSize int = 10, -- page size
@PageIndex int = 1, -- page number
@OrderType bit = 0, -- Set the sorting type, non-0 values are in descending order
@strWhere varchar(2000) = '' -- query conditions (note: do not add where)
AS
declare @strSQL varchar(6000) -- main statement
declare @strTmp varchar(1000) -- temporary variable
declare @strOrder varchar(500) -- sorting type
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec(@strSQL)
GO