SQL Server Convert Cursor to Set Based

written by MSSQL Tips September 10, 2011 0 comments

Overview

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.

Explanation

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
go
 
create procedure GetOrderData @SalesOrderID int
as
begin
   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
end
go
 
exec GetOrderData @SalesOrderID = 51721
go

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
go
 
alter procedure GetOrderData @SalesOrderID int
as
begin
   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
   begin
      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
   end
 
   close DetailCursor;
  deallocate DetailCursor;
end
go
 
exec GetOrderData @SalesOrderID = 51721
go

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

Resources

Community

Subscribe