SQL Server Convert Cursor to Set Based



The T-SQL programming language is a declarative, set based language. What does this mean? You issue a query for the results you want and the database engine sweats the details on how best to satisfy the request. Developers that are generally unfamiliar with declarative queries usually turn towards looping mechanisms, such as cursors, which retrieve data one row at a time resulting in query inefficiencies.


Cursor logic is one of the common performance killers I come across when troubleshooting database issues. If a cursor is working with few rows, it may not ever manifest itself as an issue. Invariably, you will end up working with large sets of data and, at this point, you will likely see cursor logic begin to cause grief. I once had to troubleshoot a conversion script for a customer that worked with data from 3 related tables. The script processed the first table with a cursor, looked for related entries in a 2nd table which was also looped through with a cursor, followed by the 3rd table, also accessed using a cursor. Total number of rows to process was roughly 120,000. Total execution time was three days (I only know because I had a morbid curiosity to find out, so I let the script run to completion). I re-wrote the script querying the tables as a set (basically INNER JOINing the 3 tables) and I was able to cut the conversion down to about 1 minute. Needless to say, the customer was happy.

Let's examine a simple example. The following stored procedure is used to get some information about an order in AdventureWorks. It uses a set based query to retrieve the results

set statistics time on
create procedure GetOrderData @SalesOrderID int
 set nocount on
 select OH.OrderDate, OH.DueDate, OH.ShipDate, D.ProductID, P.Name
 from Sales.SalesOrderHeader as OH
 inner join Sales.SalesOrderDetail as D on D.SalesOrderID = OH.SalesOrderID
 inner join Production.Product as P on P.ProductID = D.ProductID
 where OH.SalesOrderID = @SalesOrderID
exec GetOrderData @SalesOrderID = 51721

The resulting execution is extremely efficient returning all 72 rows for the resultset in about 5 milliseconds.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

Now let's re-write this procedure to retrieve the same results using a cursor to spin through the sales order detail rows and examine the difference.

set statistics time on
alter procedure GetOrderData @SalesOrderID int
 set nocount on
 declare @OrderDate datetime
 declare @DueDate datetime
 declare @ShipDate datetime
 declare @ProductID int
 declare @Name nvarchar(50)
 select @OrderDate = OrderDate,
            @DueDate = DueDate,
            @ShipDate = ShipDate
 from Sales.SalesOrderHeader
 where SalesOrderID = @SalesOrderID
 declare DetailCursor
 cursor for
  select ProductID
  from Sales.SalesOrderDetail
  where SalesOrderID = @SalesOrderID
 open DetailCursor
 fetch DetailCursor into @ProductID
 while @@FETCH_STATUS = 0
  select @Name = Name
  from Production.Product
  where ProductID = @ProductID
  select @OrderDate as OrderDate, 
    @DueDate as DueDate, 
    @ShipDate as ShipDate, 
    @ProductID as ProductID,
    @Name as Name
  fetch next from DetailCursor into @ProductID
 close DetailCursor;
 deallocate DetailCursor;
exec GetOrderData @SalesOrderID = 51721

The cursor approach in this stored procedure takes longer to produce the same query result

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 1886 ms.

While this is not an extreme case, you may find the cursor approach start to exponentially degrade as the number of rows to process grows. I generally avoid cursors in production code except under specialized circumstances.

Additional Information

Last Update: 9/10/2011

Comments For This Article


get free sql tips
agree to terms

Learn more about SQL Server tools