Then I tested the INSERT and DELETE commands, using ranking functions with and without sorting (Listing 7 and Listing 8).
— Listing 7. Performance tests 1 (Inserts, using SELECT …INTO).
— Query 1: Using ORDER BY orderID.
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].RankingFunctionsInserts’) AND type in (N’U’))
DROP TABLE RankingFunctionsInserts;
GO
SELECT ROW_NUMBER () OVER (ORDER BY OrderID) AS rowNum, OrderID
INTO RankingFunctionsInserts
FROM RankingFunctions;
— Drop table RankingFunctionsInserts and run Query 2.
— Query 2: Without sorting.
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS rowNum, OrderID
INTO RankingFunctionsInserts
FROM RankingFunctions;
— Drop table RankingFunctionsInserts and run Query 3.
— Query 3: Using a pre-2005 solution.
SELECT IDENTITY(int,1,1) AS rowNum, orderID
INTO RankingFunctionsInserts
FROM RankingFunctions;
Each of the three queries in Listing 7 inserts the generated row number and orderID into the RankingFunctionsInserts table, using the SELECT…INTO statement. (This technique is very helpful when you trying to create pseudo-arrays in SQL.)
For the sake of curiosity, I tested a solution with an IDENTITY column (Query 3). That solution is very common in pre-2005 versions of SQL Server.
— Listing 8. Performance tests 2 (Delete every fifth row in the RankingFunctions table).
— Query 1: Without sorting.
— Run the script from Listing 6 to insert 2,621,440 rows into RankingFunctions.
WITH originalOrder AS
(SELECT ROW_NUMBER ( ) OVER (ORDER BY (SELECT 1)) AS rowNum, OrderID
FROM RankingFunctions)
DELETE originalOrder WHERE rowNum%5 = 0;
— Query 2: With ORDER BY OrderID.
— Run the script from Listing 6 to insert 2,621,440 rows into RankingFunctions.
WITH originalOrder AS
(SELECT ROW_NUMBER ( ) OVER (ORDER BY OrderID) AS rowNum, OrderID
FROM RankingFunctions)
DELETE originalOrder WHERE rowNum%5 = 0;
Deleting every Nth row or duplicates in the table are common tasks for a DBA or database programmer. In Listing 8, I used CTE to delete every fifth row in the RankingFunctions table.
Test Results
Here are the results that I got on a regular Pentium 4 desktop computer with 512 MB RAM running Windows 2000 Server and Microsoft SQL Server 2005 Developer Edition:
ROW_NUMBER() |
RANK() |
DENSE_RANK() |
NTILE(3) |
|
INSERT 2,621,440 rows |
||||
without sorting |
5 sec. |
N/A |
N/A |
35 sec. |
with sorting |
14 sec. |
14 sec. |
14 sec. |
40 sec. |
with IDENTITY |
8 sec. |
N/A |
N/A |
N/A |
DELETE each 5th row |
||||
without sorting |
5 sec. |
|||
with sorting |
24 sec. |