Avoid Cursors

Avoid Cursors 

There may be times when you need to loop through a resultset a row at a time and perform a certain action for each row. The most obvious way to solve this task is to use a SQL Cursor.

Whilst cursors may seem like a good idea they can often cause your database application problems as they can lock the tables that are used to populate the cursor whilst the rows in the cursor are looped through. Depending on the action that you are performing on each row this can take a considerable time. The effect of this is that tables cannot be updated or accessed by other users whilst the cursor is open.

Lots of articles have beaten up on SQL Server cursors -- database objects that manipulate data in a set on a row-by-row basis. I wish cursors had never been introduced. But, unfortunately, cursors are a fact of life. Problems with cursors include extending locks, their inability to cache execution plans and CPU/RAM overhead. Many T-SQL programmers and DBAs use the cursors in their code knowingly or un-knowingly. They do not know how to successfully loop over records without the need for cursors. In this tip,

Some alternatives to cursors that provide looping functionality.

Method 1: Temp table with identity column

In the first approach, we can use a temp table with an identity column added to allow for row-by-row selection. If you're performing an INSERT/UPDATE/DELETE, be sure to use the explicit transactions. This vastly reduces the load on your log file by committing per loop, and it prevents huge rollbacks in the case of failure.

-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #ActiveEmployee (
 RowID int IDENTITY(1, 1),
 EmployeeID int,
 FirstName varchar(30),
 LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @EmployeeID int, @FirstName varchar(30), @LastName varchar(30)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #ActiveEmployee (EmployeeID, FirstName, LastName)
SELECT EmployeeID, FirstName, LastName
FROM Employee
WHERE Active = 1

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

create clustered index idx_tmp on #ActiveEmployee(RowID) WITH FILLFACTOR = 100

/*

Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times.

*/

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @EmployeeID = EmployeeID, @FirstName = FirstName, @LastName = LastName
 FROM #ActiveCustomer
 WHERE RowID = @RowCount

EXEC MyStoredProc @EmployeeID, @FirstName, @LastName

 SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #ActiveEmployee

This gives us the benefits that the Customer table is not locked as we are looping through our resultset so other queries on the Customer table that are submitted by other users will execute much faster. We will also have a faster operating SQL script by avoiding cursors which are slow in themselves.

Method 2: Temp table without ID

In the second approach, we use a temp table without an identity column and simply grab the top row to process, then loop until we find no more rows to process. If you're performing an INSERT/UPDATE/DELETE, again, be sure to use the explicit transactions to vastly reduce the load on your log file by committing per loop, which prevents huge rollbacks in the case of failure.

set nocount on

declare @i int --iterator
declare @iRwCnt int --rowcount
declare @sValue varchar(100)
set @i = 1 --initialize

create table #tbl(Value varchar(100))

insert into #tbl(Value)
select name
from master..sysdatabases (nolock)

set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work

create clustered index idx_tmp on #tbl(Value) WITH FILLFACTOR = 100
/*

Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times.

*/

while @iRwCnt > 0
begin
select top 1 @sValue = Value from #tbl
set @iRwCnt = @@ROWCOUNT --ensure that we still have data

if @iRwCnt > 0
begin

--begin tran
print 'My Value is ' + @sValue --replace with your operations on this value
--commit tran

delete from #tbl where value = @sValue --remove processed record
end
end

drop table #tbl

This article gives you some good reasons why cursors in SQL Server should be avoided as well as some alternatives that give you looping functionality. Keep in mind that SQL Server is designed around batch processing, so the less you loop, the faster your system will run.