Table structure:
Copy Code code as follows:
CREATE TABLE [dbo]. [Xtest] (
[ID] [bigint] IDENTITY (1,1) not NULL,
[XName] [nvarchar] (m) NULL,
[Xdemo] [nvarchar] () 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) O N [PRIMARY]
) on [PRIMARY]
Insert 500,000 data:
Copy Code code 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 the content of the +cast as @i ' + ')
Set @i=@i+1
End
In addition, an index is established for XName.
The stored procedure is as follows:
Copy Code code as follows:
-- =============================================
--Author: <jiangrod>
--Create Date: <2010-03-03>
--Description: <sql2000 General paging Stored procedure > Calling Method: sp_pager2000 ' xtest ', ' * ', ' id ', ' ORDER by ID ASC ', ' xname like ' '%222name% ' ' , 3,20,0,0
-- =============================================
ALTER PROCEDURE [dbo]. [sp_pager2000]
@tblName varchar (255),--table name such as: ' Xtest '
@strGetFields varchar (1000) = ' * ',--the column that needs to be returned: ' Xname,xdemo '
@pkName nvarchar = ' ID ',--primary Key name
@strOrder varchar (255) = ',--sorted field name such as: ' ORDER by id DESC '
@strWhere varchar (1500) = ',--Query criteria (note: Do not add where) such as: ' XName like '%222name% '
@pageIndex int = 1,--page number: 2
@pageSize int = 20, number of records per page: 20
@recordCount int=0 out,--Total records
@doCount bit=0-not 0 statistics, 0 is not statistical (statistics can 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 Record bar number
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 versions common paging stored procedures > Invocation methods: sp_pager2005 ' xtest ', ' * ', ' ORDER by ID ASC ', ' xname like ' '%222name% ' ' , 2,20,0,0
-- =============================================
ALTER PROCEDURE [dbo]. [sp_pager2005]
@tblName varchar (255),--table name such as: ' Xtest '
@strGetFields varchar (1000) = ' * ',--the column that needs to be returned: ' Xname,xdemo '
@strOrder varchar (255) = ',--sorted field name such as: ' ORDER by id DESC '
@strWhere varchar (1500) = ',--Query criteria (note: Do not add where) such as: ' XName like '%222name% '
@pageIndex int = 1,--page number: 2
@pageSize int = 20, number of records per page: 20
@recordCount int output,--Total number of records
@doCount bit=0-not 0 statistics, 0 is not statistical (statistics can affect efficiency)
As
DECLARE @strSQL varchar (5000)
declare @strCount nvarchar (1000)
--Total Record bar number
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)
Tested the above two stored procedures, test environment: Core 2 Duo 2.20gh,1g memory, SQL SERVER2008
Paging query test for 500,000 data
Query conditions are: XName like '%222name% '
"Page1:2 sec 937 milliseconds": Indicates when the first page is used, and so on
Do not use Row_number () to count every page:
Page1:2 seconds 937 milliseconds
Page2:3 seconds 140 milliseconds
Page3:3 seconds 453 milliseconds
Page4:3 seconds 609 milliseconds
Page5:3 seconds 843 milliseconds
Page6:4 seconds 156 milliseconds
Do not use Row_number () only the first page statistics:
Page1:2 seconds 937 milliseconds
page2:0 seconds 343 milliseconds
page3:0 seconds 593 milliseconds
page4:0 seconds 812 milliseconds
Page5:1 seconds 46 milliseconds
Page6:1 seconds 281 milliseconds
Use Row_number () to count every page:
Page1:2 seconds 937 milliseconds
Page2:3 seconds 31 milliseconds
Page3:3 seconds 156 milliseconds
Page4:3 seconds 296 milliseconds
Page5:3 seconds 421 milliseconds
Page6:3 seconds 515 milliseconds
Use Row_number () only the first page of statistics:
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: If the total number of statistics recorded in the stored procedure will seriously affect the efficiency, the same query condition record total must be the same, so if the first page is executed after the total number of records saved, click on the other page of the time command stored
The process no longer counts the total number of records will greatly improve system performance. The test results show that each time the total number of records used Row_number () and not using the row_number () difference is not too large, if only the first statistical total number of records used Row_number () will
is much more efficient than not using row_number ().
