Ultra paging Stored Procedure of tens of millions of pages

These two days I tested the sql2005 dedicated paging storage process written a few days ago. When the data volume reaches more than, the efficiency is quite low. Every execution takes about 8 seconds (CPU: q6600 ). However, in the case of over million data records, the performance was quite good. I searched for the data on the Internet and found that the two were actually one, but the latter was much more flexible, for reference only. Copy Save

 /***** Object: storedprocedure [DBO]. [getrecordfrompage] script Date: 07/23/2008 18:42:05 ******/  Set Ansi_nulls On  Go  Set Quoted_identifier On  Go  /* Function name: getrecordfrompage function: Get the data parameter description on the specified page: @ tblname: name of the table containing data @ fldname key field name @ pagesize number of records per page @ page number to be obtained by pageindex @ ordertype sorting type, 0-ascending, 1-descending order @ strwhere query condition (Note: Do not add where )*/  Alter  Procedure [ DBO ]. [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. If the value is not 0, sort it in descending order.      @ Strwhere       Varchar (2000) = ''   -- Query condition (Note: Do not add where)  As  Declare   @ Strsql     Varchar (6000) -- Subject sentence  Declare   @ Strtmp     Varchar (1000) -- Temporary Variable  Declare   @ Strorder   Varchar (500) -- Sorting type  If   @ Ordertype ! = 0 Begin  Set   @ Strtmp = '<(Select min'  Set   @ Strorder ='Order [' + @ Fldname + '] Desc'  End  Else  Begin  Set   @ Strtmp = '> (Select Max'  Set   @ Strorder = 'Order [' + @ 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 )

This is the most efficient one I have ever seen, but its order by statement is not a problem. It cannot be specified, and there is still no total number of records returned. So we have the following improvements.

If you need to change the total number of pages, but it is enough now. Of course, when the data volume reaches 10 million, it is a little less efficient than the above, but it is not too bad,

We recommend the following.CopySave

 /***** Object: storedprocedure [DBO]. [usp_getrecordfrompage] script Date: 07/23/2008 18:42:37 ******/  Set Ansi_nulls On  Go  Set Quoted_identifier On  Go Create         Procedure [ DBO ]. [Usp_getrecordfrompage] @ Tblname         Varchar (1000 ), -- Table name      @ Selectfieldname      Varchar (4000 ), -- Field name to be displayed (do not add select)      @ Strwhere         Varchar (4000 ), -- Query condition (Note: Do not add where)      @ Orderfieldname        Varchar (255 ), -- Sort index field name      @ Pagesize         Int , -- Page size     @ Pageindex        Int = 1, -- Page number      @ Irowcount        Int   Output , -- Total number of returned records      @ Ordertype        Bit = 0 -- Set the sorting type. If the value is not 0, sort it in descending order.  As  Declare   @ Strsql      Varchar (4000) -- Subject sentence  Declare   @ Strtmp      Varchar (4000) -- Temporary Variable  Declare   @ Strorder   Varchar (400) -- Sorting type  Declare   @ Strrowcount      Nvarchar (4000) -- Statement used to query the total number of records  Set   @ Orderfieldname = Ltrim ( Rtrim ( @ Orderfieldname )) If   @ Ordertype ! = 0 Begin  Set   @ Strtmp = '<(Select min'  Set   @ Strorder = 'ORDER' +@ Orderfieldname + 'Desc'  End  Else  Begin  Set   @ Strtmp = '> (Select Max'  Set   @ Strorder = 'ORDER' + @ Orderfieldname + 'Asc'  End  Set   @ Strsql = 'Select top' + Str ( @ Pagesize ) + @ Selectfieldname + 'From' + @ Tblname + 'Where' + @ Orderfieldname + @ Strtmp + '(' + Right ( @ Orderfieldname , Len ( @ Orderfieldname )- Charindex ( '.' , @ Orderfieldname ) + ') From (select top' + Str (( @ Pageindex -1 )* @ Pagesize ) + @ Orderfieldname + 'From' + @ Tblname + @ Strorder + ') As tbltmp )' + @ Strorder  If   @ Strwhere ! = ''  Set   @ Strsql = 'Select top' + Str ( @ Pagesize ) + @ Selectfieldname + 'From' + @ Tblname + 'Where' + @ Orderfieldname +@ Strtmp + '(' + Right ( @ Orderfieldname , Len ( @ Orderfieldname )- Charindex ( '.' , @ Orderfieldname ) + ') From (select top' + Str (( @ Pageindex -1 )* @ Pagesize ) + @ Orderfieldname + '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 ) + @ Selectfieldname + 'From' + @ Tblname + @ Strtmp + '' + @ Strorder  End  Exec ( @ Strsql ) If   @ Strwhere ! = ''  Begin  Set   @ Strrowcount ='Select @ irowcount = count (*) from' + @ Tblname + 'Where' + @ Strwhere  End  Else  Begin  Set   @ Strrowcount = 'Select @ irowcount = count (*) from' + @ Tblname  End  Exec Sp_executesql @ Strrowcount , N' @ irowcount int out' , @ Irowcount Out