SQL Server General Stored Procedure paging code (with ROW_NUMBER () and no ROW_NUMBER () for Performance Analysis

Table Structure:
Copy codeThe Code is as follows:
Create table [dbo]. [Xtest] (
[ID] [bigint] IDENTITY (1, 1) not null,
[XName] [nvarchar] (50) NULL,
[XDemo] [nvarchar] (500) NULL,
CONSTRAINT [PK_xtest] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert 0.5 million data records:
Copy codeThe Code is as follows:
Declare @ I int
Set @ I = 1
While (@ I <500001)
Begin
Insert into dbo. xtest (XName, XDemo) values (CAST (@ I as varchar) + 'name' + CAST (@ I as varchar), 'This is' + CAST (@ I as varchar) + 'content ')
Set @ I = @ I + 1
End

Create an index for XName.
The stored procedure is as follows:
Copy codeThe Code is as follows:
-- ===================================================== ======
-- Author: <jiangrod>
-- Create date: <2010-03-03>
-- Description: <SQL2000 General paging stored procedure> call method: sp_Pager2000 'xtest', '*', 'id', 'order by id asc ', 'xname like ''' % 222 name % ''',
-- ===================================================== ======
Alter procedure [dbo]. [sp_Pager2000]
@ TblName varchar (255), -- table name, for example, 'xtest'
@ StrGetFields varchar (1000) = '*', -- the column to be returned is 'xname, xdemo'
@ PkName nvarchar (50) = 'id', -- primary key name
@ StrOrder varchar (255) = '', -- Name of the sorted field, for example, 'order by id desc'
@ StrWhere varchar (1500) = '', -- Query condition (Note: Do not add where), for example, 'xname like ''' % 222 name % '''
@ PageIndex int = 1, -- page number, for example, 2
@ PageSize int = 20, -- number of records per page, for example, 20
@ RecordCount int = 0 out, -- total number of records
@ DoCount bit = 0 -- if the value is not 0, statistics are collected. If the value is 0, no statistics are collected (Statistics affect efficiency)
AS
Declare @ SQL nvarchar (4000)
Declare @ sqltemp nvarchar (1000)
Set @ SQL = 'from' + @ tblName
If (@ strWhere! = '') Set @ SQL = @ SQL + 'where' + @ strWhere
Declare @ strCount nvarchar (1000)
-- Total number of records
If (@ doCount! = 0)
Begin
If (@ strWhere! = '')
Begin
Set @ strCount = 'set @ num = (select count (1) from '+ @ tblName + 'where' + @ strWhere + ')'
End
Else
Begin
Set @ strCount = 'set @ num = (select count (1) from '+ @ tblName + ')'
End
EXECUTE sp_executesql @ strCount, n' @ num INT output', @ RecordCount output
End
If @ strWhere! =''
Begin
Set @ strWhere = 'where' + @ strWhere
End
If (@ PageIndex> 1)
Begin
Set @ sqltemp = @ PKName + 'not in (Select Top' + cast (@ PageSize * (@ PageIndex-1) as nvarchar) + ''+ @ PKName +'' + @ SQL
If (@ strOrder! = '') Set @ sqltemp = @ sqltemp +'' + @ strOrder
Set @ sqltemp = @ sqltemp + ')'
Set @ SQL = 'select Top '+ cast (@ PageSize as nvarchar) + ''+ @ strGetFields +'' + @ SQL
If (@ strWhere! = '') Set @ SQL = @ SQL + 'and' + @ sqltemp
Else set @ SQL = @ SQL + 'where' + @ sqltemp
End
Else
Begin
Set @ SQL = 'select Top '+ cast (@ PageSize as nvarchar) + ''+ @ strGetFields +'' + @ SQL
End
If (@ strOrder! = '') Set @ SQL = @ SQL +'' + @ strOrder
Exec (@ SQL)

-- ===================================================== ======
-- Author: <jiangrod>
-- Create date: <2010-03-03>
-- Description: <SQL2005 and subsequent General paging stored procedures> call method: sp_Pager2005 'xtest', '*', 'order by id asc ', 'xname like ''' % 222 name % ''',
-- ===================================================== ======
Alter procedure [dbo]. [sp_Pager2005]
@ TblName varchar (255), -- table name, for example, 'xtest'
@ StrGetFields varchar (1000) = '*', -- the column to be returned is 'xname, xdemo'
@ StrOrder varchar (255) = '', -- Name of the sorted field, for example, 'order by id desc'
@ StrWhere varchar (1500) = '', -- Query condition (Note: Do not add where), for example, 'xname like ''' % 222 name % '''
@ PageIndex int = 1, -- page number, for example, 2
@ PageSize int = 20, -- number of records per page, for example, 20
@ RecordCount int output, -- total number of records
@ DoCount bit = 0 -- if the value is not 0, statistics are collected. If the value is 0, no statistics are collected (Statistics affect efficiency)
AS
Declare @ strSQL varchar (5000)
Declare @ strCount nvarchar (1000)
-- Total number of records
If (@ doCount! = 0)
Begin
If (@ strWhere! = '')
Begin
Set @ strCount = 'set @ num = (select count (1) from '+ @ tblName + 'where' + @ strWhere + ')'
End
Else
Begin
Set @ strCount = 'set @ num = (select count (1) from '+ @ tblName + ')'
End
EXECUTE sp_executesql @ strCount, n' @ num INT output', @ RecordCount output
End
If @ strWhere! =''
Begin
Set @ strWhere = 'where' + @ strWhere
End
Set @ strSQL = 'select * FROM (SELECT ROW_NUMBER () OVER ('+ @ strOrder +') as rowid ,'
Set @ strSQL = @ strSQL + @ strGetFields + 'from ['+ @ tblName +'] '+ @ strWhere
Set @ strSQL = @ strSQL + ') AS sp where rowid between' + str (@ PageIndex-1) * @ PageSize + 1)
Set @ strSQL = @ strSQL + 'and' + str (@ PageIndex * @ PageSize)
Exec (@ strSQL)

The above two stored procedures were tested in the test environment: Core 2 dual-core 2.20Gh, 1 GB memory, SQL SERVER2008
Paging query of 0.5 million data entries
The query condition is: xname like '% 222 name %'
"Page1: 2 seconds 937 ms": indicates the time used to open the first page, and so on.
Statistics are collected on every page without ROW_NUMBER:
Page1: 2 seconds 937 milliseconds
Page2: 3 seconds, 140 milliseconds
Page3: 3 seconds, 453 milliseconds
Page4: 3 seconds, 609 milliseconds
Page5: 3 seconds 843 Ms
Page6: 4 seconds 156 Ms
If ROW_NUMBER () is not used, only the first page of statistics is used:
Page1: 2 seconds 937 milliseconds
Page2: 0 seconds 343 milliseconds
Page3: 0 seconds 593 milliseconds
Page4: 0 seconds 812 milliseconds
Page5: 1 second 46 Ms
Page6: 1 second 281 Ms
Statistics on each page using ROW_NUMBER:
Page1: 2 seconds 937 milliseconds
Page2: 3 seconds 31 Ms
Page3: 3 seconds, 156 milliseconds
Page4: 3 seconds, 296 milliseconds
Page5: 3 seconds 421 Ms
Page6: 3 seconds 515 Ms
Use ROW_NUMBER () for statistics only on the first page:
Page1: 2 seconds 937 milliseconds
Page2: 0 seconds 218 milliseconds
Page3: 0 seconds 359 milliseconds
Page4: 0 seconds 468 milliseconds
Page5: 0 seconds 578 milliseconds
Page6: 0 seconds 687 milliseconds
Conclusion: In the stored procedure, if the total number of records is counted each time, the efficiency will be seriously affected. The total number of records with the same query condition must be the same, therefore, if the total number of records is saved after the first page is executed
If you do not count the total number of records, the system performance will be greatly improved. The test results show that the difference between the total number of records to be counted each time using ROW_NUMBER () and the number of records not using ROW_NUMBER () is not big.
It is much more efficient than not using ROW_NUMBER.