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.
Hi Jonh !
Excelent article !
I have an SQLSERVER table with 20 bilions of records (yes, billions), and I will need to consolidate data on these table (ex: I have in original table four records of title: 3,4,5,6 and I will neet to convert it in one record with “min title” = 3 and “max title” = 6.
Do you think FAST FOWARD CURSOR is a good idea to process this large/enormous table? Or do you think there is a better another solution without using cursors?
Best regards,
Sergio Coutinho
São Paulo -Brazil
Hi Sergio,
For looping through billions of records, the most concern is the server resource. Set operation without proper indexes, static cursors, and key set cursors might eat up all your resource in tempdb. If large writing in one tranction is involved, the lock overheads will suck the memory and slow down the process. If SET operation can be used(without any hash joins, table spills, large data modification in one transaction, creating new indexes, etc), set operation wil be better. If not, only dynamic cursor can be used. Fast forward cursor has less overhead than other regular read-writable dynamic cursors.
I was not able to draw a clear picture from your example. Please email me if you have further questions.
John H