SQL SERVER – Convert Cursor to Set Based Insert

During a recent Comprehensive Database Performance Health Check engagement with my client, we came across a significant issue related to cursor usage for insert operations, which had a detrimental impact on the server’s overall performance. Cursors, while useful in certain situations, can be slow and resource-intensive, especially when dealing with large result sets. As a result, exploring alternative approaches to improve performance is essential. In this blog post, we will focus on the issue of cursor-based inserts and demonstrate how to convert them into set-based operations.

Sample Setup

First, we will create a sample table and populate it with some data. The following script creates a table called Employee with three columns: EmployeeID, FirstName, and LastName.

CREATE TABLE Employee (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);
GO
INSERT INTO Employee VALUES (1, 'John', 'Doe');
INSERT INTO Employee VALUES (2, 'Jane', 'Doe');
INSERT INTO Employee VALUES (3, 'Bob', 'Smith');
INSERT INTO Employee VALUES (4, 'Alice', 'Jones');
GO
CREATE TABLE EmployeeCopy (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Cursor-based Insert

Next, we will create a cursor that iterates through the Employee table and performs an insert operation on each row. The following script demonstrates how to do this:

This cursor iterates through the Employee table and retrieves the EmployeeID, FirstName, and LastName columns for each row. It then performs an insert operation on the EmployeeCopy table, which is a copy of the Employee table. Note that the EmployeeCopy table must be created before running this script.

DECLARE @EmployeeID INT;
DECLARE @FirstName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName
FROM Employee;
OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO EmployeeCopy (EmployeeID, FirstName, LastName)
    VALUES (@EmployeeID, @FirstName, @LastName);
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;
END
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Set based Insert

Now, let’s convert the cursor-based insert to a set-based insert. The following script demonstrates how to do this:

INSERT INTO EmployeeCopy (EmployeeID, FirstName, LastName)
SELECT EmployeeID, FirstName, LastName
FROM Employee;

This script performs a select statement that retrieves the EmployeeID, FirstName, and LastName columns from the Employee table and inserts them into the EmployeeCopy table. This approach is set-based, meaning that it operates on the entire result set at once rather than iterating through it one row at a time.

Performance Comparison

Finally, let’s compare the performance of the cursor-based insert and the setbased insert. We can use the following script to measure the execution time of each approach:

DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;
DECLARE @Duration INT;
SET @StartTime = GETDATE();
-- ...
-- Cursor-based insert
-- ...
SET @EndTime = GETDATE();
SET @Duration = DATEDIFF(MILLISECOND, @StartTime, @EndTime);
PRINT 'Cursor-based insert duration: ' + CAST(@Duration AS VARCHAR(10)) + 'ms';
SET @StartTime = GETDATE();
-- ...
-- Setbased insert
-- ...
SET @EndTime = GETDATE();
SET @Duration = DATEDIFF(MILLISECOND, @StartTime, @EndTime);
PRINT 'Set-based insert duration: ' + CAST(@Duration AS VARCHAR(10)) + 'ms';

This script measures the execution time of the cursor-based insert and the set-based insert and prints the results to the console. Note that you should uncomment the cursor-based insert code in order to measure its execution time.

When we run this script, we get the following output:

Cursor-based insert duration: 22ms
Set-based insert duration: 0ms

As you can see, the set-based insert operation is much faster than the cursor-based insert operation, taking only 0ms to complete compared to 18ms for the cursor-based insert. This is because the set-based insert operates on the entire result set at once, whereas the cursor-based insert iterates through the result set one row at a time, which can be slow and resource-intensive.

Conclusion

In this blog post, we demonstrated how to convert a cursor to a set-based insert operation in SQL Server. We created a sample table, populated it with some data, and then created a cursor that iterated through the data and performed an insert operation on each row. We then converted the cursor to a set-based insert operation and compared the performance of the two approaches. We found that the set-based insert operation was much faster and more efficient than the cursor-based insert operation, highlighting the benefits of using set-based operations in SQL Server.

You can always reach out to me via YouTube Channel.

Reference: Pinal Dave (http://blog.SQLAuthority.com)