Scalable “Cursors” in SQL

In T-SQL stored procedures and user-defined functions, there will be times when you need to iterate through a recordset/table, to perform some operation on each record.

That’s what cursors are made for. Except that they don’t scale at all, so you should never use them for sets of more than a few dozen records.

What you can do instead is to “build your own cursor” using a temporary table or table variable, which does scale, even to processing millions of records:

DECLARE @Count int
DECLARE @AccountID char(12)
DECLARE @UserField1 varchar(128)

CREATE TABLE #tblAccount (
     [AccountID] [char](12),
     [UserField1] [varchar](128)
)

CREATE INDEX PIndex
ON #tblAccount (AccountID)

INSERT INTO #tblAccount
SELECT AccountID
FROM sysdba.Account
ORDER BY AccountID

SELECT @Count = Count(*) FROM #tblAccount

WHILE @Count > 0
     BEGIN
          SET @UserField1 = iCount

          SELECT @AccountID = MIN(AccountID) FROM #tblAccount

          UPDATE sysdba.Account
          SET UserField1 = @UserField1
          WHERE AccountID = @AccountID

          DELETE FROM #tblAccount
          WHERE AccountID = @AccountID

          SET @Count = @Count - 1
     END

DROP TABLE #tblAccount

Deleting each record after you’ve processed it speeds up the processing by (IIRC) around an order of magnitude. Using a temporary table and putting an index on the field you’re using for the primary key has similarly positive effects. (You can’t explicitly put an index on a table variable, but you can do so implicitly just by setting a primary key for it.)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: