SQL Cursors - how to avoid them
Introduction
There may be times when you need to loop through a resultset a row at a time and perform a certain action for each row. The most obvious way to solve this task is to use a SQL Cursor.
Whilst cursors may seem like a good idea they can often cause your database application problems as they can lock the tables that are used to populate the cursor whilst the rows in the cursor are looped through. Depending on the action that you are performing on each row this can take a considerable time. The effect of this is that tables cannot be updated or accessed by other users whilst the cursor is open.
I know one SQL specialist who when interviewing for DBA roles asks the candidate to write the syntax for using a cursor. If the candidate knows it then it is a negative point against them as they shouldn't use cursors frequently enough so that they can remember the syntax.
Whilst many SQL books advise you not to use SQL cursors, not many provide alternative solutions. This article shows how alternatives to cursors can be implemented.
An example of a SQL Cursor that we want to avoid
DECLARE @CustomerID int DECLARE @FirstName varchar(30), @LastName varchar(30) -- declare cursor called ActiveCustomers DECLARE ActiveCustomers Cursor FOR SELECT CustomerID, FirstName, LastName FROM Customer WHERE Active = 1 -- Open the cursor OPEN ActiveCustomers -- Fetch the first row of the cursor and assign its values into variables FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName -- perform action whilst a row was found WHILE @@FETCH_STATUS = 0 BEGIN Exec MyStoredProc @CustomerID, @Forename, @Surname -- get next row of cursor FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName END -- Close the cursor to release locks CLOSE ActiveCustomers -- Free memory used by cursor DEALLOCATE ActiveCustomers
Cursor alternative 1: Using the SQL WHILE loop
SQL provides us with the WHILE looping structure. This can be utilised with a temporary table that enables us to avoid using a cursor:
-- Create a temporary table, note the IDENTITY -- column that will be used to loop through -- the rows of this table CREATE TABLE #ActiveCustomer ( RowID int IDENTITY(1, 1), CustomerID int, FirstName varchar(30), LastName varchar(30) ) DECLARE @NumberRecords int, @RowCount int DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30) -- Insert the resultset we want to loop through -- into the temporary table INSERT INTO #ActiveCustomer (CustomerID, FirstName, LastName) SELECT CustomerID, FirstName, LastName FROM Customer WHERE Active = 1 -- Get the number of records in the temporary table SET @NumberRecords = @@ROWCOUNT SET @RowCount = 1 -- loop through all records in the temporary table -- using the WHILE loop construct WHILE @RowCount <= @NumberRecords BEGIN SELECT @CustomerID = CustomerID, @FirstName = FirstName, @LastName = LastName FROM #ActiveCustomer WHERE RowID = @RowCount EXEC MyStoredProc @CustomerID, @FirstName, @LastName SET @RowCount = @RowCount + 1 END -- drop the temporary table DROP TABLE #ActiveCustomer
We can see the above code gives the same functionality as the first code example but without using a cursor. This gives us the benefits that the Customer table is not locked as we are looping through our resultset so other queries on the Customer table that are submitted by other users will execute much faster. We will also have a faster operating SQL script by avoiding cursors which are slow in themselves.
Cursor Alternative 2: Using User Defined Functions
Cursors are sometimes used to perform a calculation on values that come from each row in its rowset. This scenario can also be achieved by replacing a Cursor with a User Defined Function. An example of a User Defined Function performing a calculation is given below:
-- return a discount %age that the customer -- can recieve based on their no. and value -- of purchases CREATE FUNCTION dbo.GetDiscountLevel( @CustomerID int ) RETURNS int AS BEGIN DECLARE @DiscountPercent int DECLARE @NumberOrders int, @SalesTotal float SELECT @NumberOrders = COUNT(OrderID), @SalesTotal = SUM(TotalCost) FROM Sales WHERE CustomerID = @CustomerID IF @SalesTotal > 5000.00 AND @NumberOrders > 5 SET @DiscountPercent = 5 ELSE BEGIN IF @SalesTotal > 3000.00 AND @NumberOrders > 3 SET @DiscountPercent = 3 ELSE SET @DiscountPercent = 0 END Return @DiscountPercent END
An example of this function being used to replace a cursor might look something like:
SELECT FirstName, LastName, dbo.GetDiscountLevel(CustomerID) As DiscountPercent FROM Customer
Conclusion
In this article we have seen how SQL Cursors can cause performance problems and affect other queries by locking tables. We have demonstrated two popular ways of avoiding the use of cursors. The WHILE loop does avoid the use of a Cursor but it still uses an iterative loop. The User Defined Function option keeps our SELECT query tidy and enables us to perform calculations using column values from our SELECT statement.
When attempting to redesign your code to avoid cursors you should always check the execution time of your scripts. Very occassionally you may find a cursor gives better performance than an alternative method of performing the same task. When doing this remember that the script execution time is not the only thing to check, impact on queries being run by other users at the same time is also a key factor.