Converting a Cursor Into a Set-Based Insert

Converting a Cursor Into a Set-Based Insert

During a recent Comprehensive Database Performance Health Check for one of our clients, we came across a significant issue related to using a cursor for insert operations, which was having a harmful effect on the server’s overall performance. Cursors can be useful in certain situations, but they can be slow and resource-intensive, especially when dealing with large result sets. As a result, it’s worth exploring alternative approaches to improve performance. In this blog post, we’ll focus on cursor-based inserts and show how to convert them into set-based operations.

Converting to a Set-Based Insert

Sample Setup

First, we’ll create a sample table and populate it with some data. The script below 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’ll create a cursor that loops through the Employee table and performs an insert on each row. The script below shows how to do this:

This cursor loops through the Employee table and retrieves the EmployeeID, FirstName, and LastName columns for each row. It then performs an insert into the EmployeeCopy table, which is a copy of the Employee table. Note that the EmployeeCopy table needs to 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 this cursor-based insert into a set-based insert. The script below shows 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 it operates on the entire result set at once, rather than looping through it one row at a time.

Performance Comparison

Finally, let’s compare the performance of the cursor-based insert against the set-based insert. We can use the script below 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. Keep in mind that you’ll need to uncomment the cursor-based insert code to measure its execution time.

Running this script gives us the following output:

Cursor-based insert duration: 22ms

Set-based insert duration: 0ms

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

Conclusion

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