Cursors Be Gone!

本文介绍了一种避免使用SQL Server中游标的方法,并通过实例演示了如何将游标转换为传统循环,以此提高性能。通过对比游标和常规循环的执行计划,展示了游标的效率较低,尤其是在处理大量数据时。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Cursors Be Gone!

By Gaby Abed, 2008/12/24

 

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.

 

Zoom in   |   Open in new window
Zoom out   |   Open in new window

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.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值