After testing, in 14483461 records, Query, 10,000 pages, 10 per page recorded in ascending order and descending first time is 0.47 seconds, the second time is 0.43 seconds, the test syntax is as follows:
Exec GetRecordfromPage News, NewsId, 10,100000
NEWS is a table name, newsid is a key field, please establish an index for the NewSID.
Related example: http://blog.9cbs.net/sun_jianhua/archive/2005/08/15/454636.aspx
/ *
Function Name: GetRecordfromPage
Function function: Get the data of the specified page
Parameter description: @TBLName contains the table name of the data
@fldname key field name
@PageSize number per page
@PageIndex to get the page number
@ORDERTYPE Sort Type, 0 - Ascending, 1 - Descending
@Strwhere Query Conditions (Note: Don't add WHERE)
Author: Tiequoon
Mailbox: sunjianhua_kki@sina.com
Creation time: 2004-07-04
Modification time: 2004-07-04
* /
Create Procedure GetRecordfromPage
@tblname varchar (255), - Table name
@fldname varchar (255), - field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Page
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (2000) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (6000) - main statement
Declare @strtmp varchar (1000) - Temporary variable
Declare @strorder varchar (500) - Sort 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)
