Part 2: Cursor advantages

So when and why should you consider using cursors? Here I'll spotlight cursor advantages.

Row-by-row operations

Cursors are best used when performing row-by-row operations that can't be accomplished with set-based operations (i.e., when you need to fire a stored procedure once per row in a table).

Here is an example of a cursor used in the Microsoft shipped routine sp_helppublication

DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR 
    SELECT pubid, name FROM syspublications WHERE name like @publication
    OPEN hC
    FETCH hC INTO @pubid, @pubname
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF is_member(N'db_owner') <> 1
        BEGIN
            exec @retcode = sp_MSreplcheck_pull
                @publication = @pubname,
                @raise_fatal_error = 0,
                @given_login = @username
 END   
        IF (is_member(N'db_owner') = 1) OR (@retcode = 0 AND @@error = 0)
            INSERT INTO #accessiblepubs values(@pubid)
        FETCH hC INTO @pubid, @pubname
    END
    CLOSE hC
    DEALLOCATE hC

This same procedure can be accomplished in a singly nested while loop, but cursors are a better option for performance. For more than one nested loop, you should use a while loop.

Here is an example with the above batch rewritten as a while loop.

DECLARE @count int
DECLARE @pubid int
DECLARE @publication varchar(10)
DECLARE @pubname varchar(10)
DECLARE @retcode int
SET @publication='pubs'
DECLARE @username sysname
SET @username=suser_name()
SELECT @count=count(pubid) FROM pubs.dbo.syspublications WHERE name like @publication
WHILE (@count > 0 )
BEGIN
IF is_member(N'db_owner') <> 1
BEGIN
SELECT @pubid=pubid, @pubname=name FROM syspublications WHERE name like @publication
exec @retcode = sp_MSreplcheck_pull @publication = @pubname, @raise_fatal_error = 0, given_login = @username
END  
IF (is_member(N'db_owner') = 1) OR (@retcode = 0 AND @@error = 0)
INSERT INTO #accessiblepubs values(@pubid)
SELECT @count=@count-1 
END

Quick and dirty

SQL developers are often under the gun to write code fast. Writing a cursor requires less mental effort than writing its set-based equivalent. Unfortunately these shortcuts often remain in production and cause problems further down the line. (Thanks for the above two observations from SQL MVPs Itzik Ben Gan and Erland Sommarskog.)

Cursors are faster than using while loops. Here is an example illustrating the timings.

USE PUBS
GO
CREATE TABLE NUMBERS (pk INT NOT NULL IDENTITY PRIMARY KEY , CHARCOL char(20))
DECLARE @intcol INT
SET @intcol=1
WHILE @intcol<8001
BEGIN
INSERT INTO NUMBERS (charcol) VALUES (@intcol)
SELECT @intcol=@intcol+1
END
DECLARE @PK VARCHAR(20)
DECLARE @datetime DATETIME
SET @datetime=GETDATE()
DECLARE test CURSOR FOR SELECT charcol  FROM NUMBERS
OPEN test
FETCH NEXT FROM test INTO @PK
WHILE (@@FETCH_STATUS =0)
BEGIN
SELECT @PK
FETCH NEXT FROM test
INTO @PK 
END
CLOSE TEST
DEALLOCATE TEST
SELECT DATEDIFF(ms, @datetime, GETDATE())
--3786 ms
DECLARE @counter INT
DECLARE @datetime DATETIME
SET @datetime=GETDATE()
SET @counter=1
WHILE (@counter< 8001)
BEGIN
SELECT charcol from numbers where pk=@counter
SELECT @counter=@counter+1
END
SELECT DATEDIFF(ms, @datetime, GETDATE())
--4676 ms – almost a full second longer

This is a trivial example, but it does illustrate the performance advantage of cursors over while loops. In our case of 8,000 rows there is nearly a one-second speed advantage in using a cursor over a while loop.

Scrolling

Classic ADO made use of cursors for scrolling or paging through a results set on the server, using server-side cursors. These cursors provided performance benefits over pure T-SQL implementations for paging through a results set. For more information on pure T-SQL implementations consult ASPFAQ.COM. In these tests the T-SQL provides marginally better performance, but cursors are faster for larger results sets. In SQL Server 2005 you can also use Common Table Expressions (CTEs) for server-side paging.

By default cursors will query the base tables with each fetch and, as such, they are always current with the most recently updated values. Here is an example:

CREATE TABLE test
(pk INT NOT NULL IDENTITY PRIMARY KEY, charcol CHAR(30))
GO
DECLARE @int INT
SET @int=1
WHILE @int <6
BEGIN
INSERT INTO test (charcol) VALUES ('this is a test '+convert(VARCHAR(2),@int))
SELECT @int=@int+1
END
GO
DECLARE @pk INT
SET @pk=1
DECLARE @charcol varchar(30)
DECLARE testcursor cursor for select pk, charcol from test
OPEN testcursor 
FETCH testcursor 
INTO @pk, @charcol
WHILE @@fetch_status=0
BEGIN
SELECT @charcol
UPDATE test SET charcol=convert(VARCHAR(2),pk)+' '+charcol 
SELECT charcol FROM test
FETCH testcursor 
INTO @pk, @charcol
END
CLOSE testcursor 
DEALLOCATE testcursor

Notice how each cursor fetch updates the value of charcol and the cursor picks up the updates in the underlying base tables.

With advantages like these, you may wonder what are the disadvantages of using cursors. Please, oh please, oh please keep reading.

Migrating to SQL Server 2005

 Home: Introduction
 Part 1: How cursors work
 Part 2: Cursor advantages
 Part 3: Cursor disadvantages
 Part 4: Cursor example

ABOUT THE AUTHOR:

Hilary Cotter Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies. Copyright 2006 TechTarget

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary