Microsoft SQL Server 2005
Performance Tuning
Best Practices

How to measure cursor performance?

 

Execute the following script step-by-step in Query Editor to measure the performance of a cursor versus inline code:

use tempdb
go
-- drop table dbo.SalesOrderDetail
select * into dbo.SalesOrderDetail
from AdventureWorks.Sales.Salesorderdetail
go
select top 10 * from dbo.SalesOrderDetail
go
-- Inline update
dbcc dropcleanbuffers
select top 500 SalesOrderID into #SalesOrder from dbo.SalesOrderDetail
update dbo.SalesOrderDetail set ModifiedDate=getdate()
where SalesOrderID in (select * from #SalesOrder)
go
-- < 1 sec

-- Cursor update
dbcc dropcleanbuffers

declare curSales CURSOR FOR select top 500 SalesOrderID from dbo.SalesOrderDetail
open curSales
declare @id int
fetch next from curSales into @id
while @@fetch_status = 0
begin
update dbo.SalesOrderDetail set modifieddate=getdate() where SalesOrderID=@ID
fetch next from curSales into @id
end
go

-- 32 seconds

 

 

The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page