Performance Comparison between ROW_NUMBER and stored procedure in sqlserver2005

Syntax: ROW_NUMBER () OVER (partition by column order by column)

In short, row_number () starts from 1 and returns a number for each group record. ROW_NUMBER () OVER (order by xlh DESC) first orders the xlh column in descending ORDER, return a sequence number for all xlh records after descending order.
Example:
Xlh row_num
1700 1
1500 2
1085 3
710 4

Row_number () OVER (partition by COL1 order by COL2) indicates grouping BY COL1 and sorting BY COL2 within the group, the value calculated by this function indicates the sequential number after each group is sorted (the continuous and unique in the group)

Instance:

Initialize data

Create table employee (empid int, deptid int, salary decimal (10, 2 ))
Insert into employee values (5500.00)
Insert into employee values (4500.00)
Insert into employee values (1900.00)
Insert into employee values (4800.00)
Insert into employee values (5, 40, 6500.00)
Insert into employee values (6, 40, 14500.00)
Insert into employee values (44500.00)
Insert into employee values (8, 50, 6500.00)
Insert into employee values (9, 50, 7500.00)

The data is displayed

Empid deptid salary
-------------------------------------------------------------
1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00

Requirement: displays the salary levels of each department according to the group of departments.

Expected results:

Empid deptid salary rank
---------------------------------------------------------------------------------
1 10 5500.00 1
2 10 4500.00 2
4 20, 4800.00, 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8, 50, 6500.00, 2

SQL script:

SELECT *, Row_Number () OVER (partition by deptid order by salary desc) rank FROM employee

 
Let's test the big database.

The methods are as follows:

 

The code is as follows:

Copy code

Select top 20 * FROM (SELECT ROW_NUMBER () OVER (order by Namec) AS RowNumber,    * FROM Dbo. mem_member) _ myResults WHERE RowNumber> 10000   SELECT * FROM (SELECT ROW_NUMBER () OVER (order by Namec) AS RowNumber,    * FROM Dbo. mem_member) _ myResults WHERE RowNumber between 10000 and 10020   WITH OrderedResults (SELECT *, ROW_NUMBER () OVER (order by Namec) as RowNumber FROM dbo. mem_member) SELECT * FROM OrderedResults WHERE RowNumber between 10000 and 10020

No matter which method is used, the performance is unsatisfactory. It takes about 6 seconds to run million data records.

2. Use a temporary table and add a stored procedure

 

The code is as follows:

Copy code

BEGIN DECLARE @ PageLowerBound int DECLARE @ PageUpperBound int                 -- Set the page bounds SET @ PageLowerBound = 10000 SET @ PageUpperBound = 10020 -- Create a temp table to store the select results Create Table # PageIndex                 ( [IndexId] int IDENTITY (1, 1) not null, [Id] varchar (18)                 )                 -- Insert into the temp table Declare @ SQL as nvarchar (4000) SET @ SQL = 'Insert INTO # PageIndex (Id )' SET @ SQL = @ SQL + 'select' SET @ SQL = @ SQL + 'top' + convert (nvarchar, @ PageUpperBound) SET @ SQL = @ SQL + 'm_id' SET @ SQL = @ SQL + 'from dbo. mem_member' SET @ SQL = @ SQL + 'Order BY NameC'                 -- Populate the temp table Exec sp_executesql @ SQL -- Return paged results Select o .* FROM Dbo. mem_member O, # PageIndex WHERE PageIndex. IndexID> @ PageLowerBound And o. [m_Id] = PageIndex. [Id] ORDER PageIndex. IndexID                 Drop table # PageIndex END

In the same case, it takes only one second to use this method. In this case, we should not say that the performance of ROW_NUMBER is not as good as that of the stored procedure.