Dec 212011
 

The performance of cursors in SQL Server has been blamed for years. Many developers rather to use a temp table to save the record set from the query and then use while statement to loop the records over the temp table. Is this a good choice? Do cursors realy perform badly? In this article, you will figure the loop will only perform well in one particular case actually. In most of the cases, cursor performs better.

There are many types of cursors. By default, dynamic cursors are used. Cursor structure is created internally when cursor is opened. This process of cursor opening is very quick since there will be no data captured at this moment. Each execution of fetch statement will evaluate the SQL in cursor declaration and retrieve one row after the row returned by previous fetch statement. So any changes after previous fetch will be reflected in current fetch. You can move cursor forward and backward. You can also modify underlying data through the cursor. This is the most expensive type of cursor in terms of performance of fetching. Adding read_only keyword in the cursor delaration to make the cursor read-only and also reduce the overhead of fetch process. This type of cursor is scrollable dynamic cursor but you cannot modify data through it. Fast_forward cursor is the fastest dynamic cursor. It can be opened instantly regardless of number of records returned. It’s similiar to regular dynamic cursor that each execution of fetch will evaluate the SQL in cursor declaration once. But you can only move the cursor forward and you cannot modify the data through this type of cursor. Static cursors make a snapshot of returned records of the SQL in declaration into tempdb while openning a cursor. Once the cursor is openned, the underlying data is unhooked with the cursor structure which means the changes to base tables will not be reflected to the cursor. This type of cursors are scrollable, read only, and consuming more storage resource at openning time but less overhead while looping. Key set cursors are in between of dynamic and static cursors in which it takes snapshot of keyset of the returned set into tempdb. In each execution of fetch, it looks up the values not in the snapshot from the base tables. The snapshot of keys are determined by unique constraints or indexes in the base tables. If uniqueness cann’t be determined, a static cursor will be created instead. Loop is the most common workaround to improve perfromance of a dynamic cursor. Usually developers use variables in the loop as filters to the query to find a row, then change the values of the variables in each iteration of the loop. Usually, the filters are on primary keys. Which one performs better? let’s have a test.

use AdventureWorks
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max)
declare c cursor local dynamic for
	select a.ModifiedDate, b.Comment
	from Sales.SalesOrderDetail a
		inner join Sales.SalesOrderHeader b on a.SalesOrderID = b.SalesOrderID
open c
fetch next from c into @ModifiedDate, @Comment
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate, @Comment
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as DynamicCursor
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max)
declare c cursor local dynamic Read_Only for
	select a.ModifiedDate, b.Comment
	from Sales.SalesOrderDetail a
		inner join Sales.SalesOrderHeader b on a.SalesOrderID = b.SalesOrderID
open c
fetch next from c into @ModifiedDate, @Comment
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate, @Comment
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as DynamicReadOnlyCursor
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max)
declare c cursor local fast_forward for
	select a.ModifiedDate, b.Comment
	from Sales.SalesOrderDetail a
		inner join Sales.SalesOrderHeader b on a.SalesOrderID = b.SalesOrderID
open c
fetch next from c into @ModifiedDate, @Comment
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate, @Comment
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as FastForwardCursor
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max)
declare c cursor static local for
	select a.ModifiedDate, b.Comment
	from Sales.SalesOrderDetail a
		inner join Sales.SalesOrderHeader b on a.SalesOrderID = b.SalesOrderID
open c
fetch next from c into @ModifiedDate, @Comment
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate, @Comment
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime())StaticCursor
go
dbcc freesystemcache('all') with no_infomsgs
if OBJECT_ID('tempdb..#1') is not null
	drop table #1
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max), @ID int, @MaxID int

create table #1(ModifiedDate datetime, Comment varchar(256), ID int identity(1,1) primary key)
insert into #1(ModifiedDate, Comment)
	select a.ModifiedDate, b.Comment
	from Sales.SalesOrderDetail a
		inner join Sales.SalesOrderHeader b on a.SalesOrderID = b.SalesOrderID
select @MaxID = MAX(ID), @ID = 1 from #1
while @ID <= @MaxID
begin
	select @ModifiedDate = ModifiedDate, @Comment = Comment
	from #1 
	where ID = @ID
	select @ID = @ID + 1
end
select datediff(millisecond, @StartTime, sysdatetime()) WhileLoop
go

Returning from pevious batches:

DynamicCursor
-------------
7752

DynamicReadOnlyCursor
---------------------
3864

FastForwardCursor
-----------------
3874

StaticCursor
------------
2595

WhileLoop
-----------
5714

In this example, static cursor only took 2595 milliseconds, which is the fastest approach. We notice that the loop approach creates snapshot of the data then perform bookmark lookups. Will it be faster if we don’t create the snapshot. Now let’s have another test again. This time, we are going to compare the performance over a heap
Data preparation:

use AdventureWorks
select * into tempSalesOrderDetail from Sales.SalesOrderDetail
create unique index tempindex on tempSalesOrderDetail(SalesOrderDetailID)

Test:

dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime
declare c cursor local fast_forward for
	select a.ModifiedDate
	from tempSalesOrderDetail a
open c
fetch next from c into @ModifiedDate
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as FastForwardCursor
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime
declare c cursor static for
	select a.ModifiedDate
	from tempSalesOrderDetail a
open c
fetch next from c into @ModifiedDate
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as StaticCursor
go

dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max), @ID int, @MaxID int
select @MaxID = MAX(SalesOrderDetailID), @ID = 1 from tempSalesOrderDetail 
while @ID <= @MaxID
begin
	select @ModifiedDate = ModifiedDate
	from tempSalesOrderDetail
	where SalesOrderDetailID = @ID
	select @ID = @ID + 1
end
select datediff(millisecond, @StartTime, sysdatetime()) as LoopWithBookMarkLookup
go

Results:

FastForwardCursor
-----------------
3145

StaticCursor
------------
2742

LoopWithBookMarkLookup
----------------------
3136

In this test, we are not capturing snapshot in while loop, however, it’s still not able to provide as better performance as a static cursor. Perfromance of the loop is close little bit better than a fast forwarding cursor. now let’s build the table in B-Tree

use AdventureWorks
drop table tempSalesOrderDetail
go
select * into tempSalesOrderDetail from Sales.SalesOrderDetail
create unique clustered index tempindex on tempSalesOrderDetail(SalesOrderDetailID)
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime
declare c cursor local fast_forward for
	select a.ModifiedDate
	from tempSalesOrderDetail a
open c
fetch next from c into @ModifiedDate
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as FastForwardCursor
go
dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime
declare c cursor static for
	select a.ModifiedDate
	from tempSalesOrderDetail a
open c
fetch next from c into @ModifiedDate
while @@FETCH_STATUS = 0
begin
	fetch next from c into @ModifiedDate
end
close c
deallocate c
select datediff(millisecond, @StartTime, sysdatetime()) as StaticCursor
go

dbcc freesystemcache('all') with no_infomsgs
go
set nocount on
declare @StartTime DateTime2(3) = sysdatetime()
declare @ModifiedDate datetime, @Comment varchar(max), @ID int, @MaxID int
select @MaxID = MAX(SalesOrderDetailID), @ID = 1 from tempSalesOrderDetail 
while @ID <= @MaxID
begin
	select @ModifiedDate = ModifiedDate
	from tempSalesOrderDetail
	where SalesOrderDetailID = @ID
	select @ID = @ID + 1
end
select datediff(millisecond, @StartTime, sysdatetime()) as LoopWithBTree
go

Results are

FastForwardCursor
-----------------
2748

StaticCursor
------------
2707

LoopWithBTree
-------------
2158

The fast forwarding cursor is little bit slower than static cursor. The fastest one is Loop/While. So we can draw a general guideline here

If query in the cursor definition is simple and with read and forward only

  • Use loop if you don’t need to take a snapshot of keys and you are searching over a clustered key.
  • Use fast forward cursor if you have to take care of the contention on tempdb
  • Use static cursor if contention on tempdb is ignorable

If your query in the cursor definition is complex, making snapshot of returned set then loop it over will not give you performance, I would recommend fast forward cursor in this case.

Share/Bookmark
Add Comment Register



 Leave a Reply

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: