Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cursors Be Gone!

By Gaby Abed, (first published: 2008/12/24)

Editor's Note: There is a great discussion that follows this article and debate it's content. Be sure to read it

Author's Note: Since I've written this article, I've become a little older and wiser, and I realize the one overriding answer that is a common thread to almost all SQL Server related questions is, "It Depends". Please view the article below (unedited since first published) in that light. And yes, as the Editor's Note above alluded, at least some conversation was generated on why we use what we use and we should always see if there's a more efficient way to do it. I will concede that there are times when cursors will do in certain situations better than other methods. So there... :-)

I decided to make an attempt at a first article to help folks wean themselves off using cursors. It is quite easy to alter your code to use traditional loops and avoid the overhead of using cursors. For this example, the performance difference would be minimal but on cursors that work on massive tables, the overhead of a cursor definitely gets in the way and a conversion to more conventional while loops would be better.

Here is a simple example of a cursor that does a dbcc checkdb on each database (I know sp_msforeachdb would work as well, but this is mainly proof of concept).

declare @query varchar(100), @dbname sysname

declare BadCursor Cursor for
  select name from sys.databases
  where name not in ('master', 'model', 'msdb', 'tempdb')

open BadCursor
fetch next from BadCursor into @dbname
while @@fetch_status = 0
begin
select @query = 'dbcc checkdb(' + quotename(@dbname) + ')'
exec(@query)
fetch next from BadCursor into @dbname
end
close BadCursor
deallocate BadCursor go

To somehow get past the fetch next feature of a cursor, you will need to make a table. As this is an example with a small amount of rows, I will use the table variable @dblist, but for larger conversions, #temp tables would work just as well:

declare @query varchar(100),
@dbname sysname
declare @dblist table (dbname sysname)

insert into @dblist(dbname) select name from sys.databases
where name not in ('master', 'model', 'msdb', 'tempdb')

while (select count(*) from @dblist) > 0
begin
select top 1 @dbname = dbname from @dblist
select @query = 'dbcc checkdb(' + quotename(@dbname) + ')'
exec(@query)
delete from @dblist where dbname = @dbname
end go

When compared side by side when displaying the estimated execution plan, the Cursor based query was 69% compared to the 31% of the more conventional loop. So at least for this example, the cursor takes at least twice as long as the reguler while loop.

As you can see in the image below, while the actual allocation/deallocation of the cursor doesn't have much of an impact, the inefficient way the query is executed is what causes this to be slow. Imagine scaling the cursor to tables with millions of rows.

 

I hope this helps everyone realize the cost of doing business with cursors. While I readily admit the fetch next feature of cursors is attractive, don't be distracted by the dazzle and realize there is always a better alternative to using them.

Good luck folks.

Editor's Note: There is a great discussion that follows this article and debate it's content. Be sure to read it

Total article views: 36884 | Views in the last 30 days: 13
 
Related Articles
FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

FORUM

Cursor - passing @dbname to INSERT STATEMENT

Concatentaion of @dbname from cursor + table name to use in FROM clause

FORUM

Cursor

cursor

FORUM

query to get dbname,totalsize,reservedsize of all databases in sql 2000

query to get dbname,totalsize,reservedsize of all databases in sql 2000

Tags
cursors    
fetch    
loop    
performance tuning    
while loop    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones