A few months back, we had a stored proc at work that was using CURSORs pretty heavily. It had CURSORs inside of CURSORs. This proc was one of our oldest, biggest, and most involved procs at over 2050 lines. On larger clients, it could be called thousands of times a day. All of the cursors were FAST_FORWARD types, which should have at least been somewhat performance friendly. However they were SELECTing from tables that had triggers which implicitly converted the cursor type to be STATIC. That’s not good, because it was locking up the SELECTed table and causing all kinds of headaches.
So the decision was made to rewrite it to use table variables and WHILE LOOPs. This sped the proc up immensely, as measured by elapsed time, at the cost of using more in-process memory. You see, when a CURSOR is used, it basically creates a temp table on TempDB and iterates through it. With a STATIC cursor, the selected table has locks placed on it in the transaction tables.
The CURSOR alternative is to create a table variable containing the rows that would have gone in the CURSOR and use a counter variable to loop through it using the IDENTITY column on the table variable. It’s still row-by-row processing, but it’s faster, more efficient, and doesn’t lock. The only downside is that it will use more RAM. So beware if you’re using SQL 2000, SQL Express, or an MSDE version as you may see performance degrade if the sqlservr process hits it’s memory ceiling.
I don’t think I need to say this, but will anyway. Changing CURSORs into WHILE LOOPs are better, but CURSORs into a set-based query is best. It’s always best to do a query in a single batch as opposed to row-by-row.
CURSOR code:
DECLARE @SomeID INT
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT SomeID
FROM dbo.TableA
WHERE ID = @ID
OPEN myCursor
FETCH NEXT FROM myCursor INTO @SomeID
WHILE(@@FETCH_STATUS <> -1)
BEGIN
–do activity
END
TABLE VARIABLE, WHILE/LOOP code:
DECLARE @rowCount INT, @currentRow INT, @SomeID INT
DECLARE @tableVariable TABLE (RowID INT IDENTITY(1,1), SomeID INT NULL)
INSERT INTO @tableVariable(SomeID)
SELECT SomeID
FROM dbo.TableA
WHERE ID = @ID
SELECT @rowCount = @@RowCount, @currentRow = 1
WHILE @currentRow<=@rowCount
BEGIN
SELECT @SomeID = SomeID FROM @tableVariable WHERE RowID = @currentRow
–do activity
SET @currentRow = @currentRow + 1
END
while loop in temp table:
DECLARE @EMPNO INT,@ESAL VARCHAR(20)
DECLARE @intIdentity AS INT
IF OBJECT_ID(‘tempdb..#tempChildTable’) IS NOT NULL
DROP TABLE #tempChildTable
create table #tempChildTable(empno int,esal varchar(20),iden int identity(1,1),Isused bit default 0)
insert into #tempChildTable(empno,esal)
select empno,esal from emp
while exists(SELECT TOP 1 1 FROM #tempChildTable where Isused=0)
Begin
IF EXISTS(SELECT TOP 1 1 FROM #tempChildTable where Isused=0)
BEGIN
SELECT TOP 1 @EMPNO =EMPNO,@ESAL = esal,@intIdentity=iden FROM #tempChildTable where Isused=0
UPDATE #tempChildTable SET esal = ‘4000’ WHERE EMPNO=@EMPNO
END
update #tempChildTable set isUsed=1 where iden = @intIdentity
END
SELECT * FROM #tempChildTable
drop table #tempChildTable
second part
Many articles have beaten up on SQL Server cursors — database objects that manipulate data in a set on a row-by-row basis — and I want to add my name to the list of people who 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 do not know how to successfully loop over records without the need for cursors. In this tip, I’ll share some alternatives to cursors that provide looping functionality.
Method 1: Temp table with identity column
In the first approach, we will 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.
set nocount on
declare @i int –iterator
declare @iRwCnt int –rowcount
declare @sValue varchar(100)
set @i = 1 –initialize
create table #tbl(ID int identity(1,1), 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(ID) 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 @i <= @iRwCnt
begin
select @sValue = Value from #tbl where ID = @i
–begin tran
print 'My Value is ' + @sValue –replace with your operations on this value
–commit tran
set @i = @i + 1
end
drop table #tbl
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
Method 3: Selecting a comma-delimited list of items
When most developers/DBAs are asked to come up with a list of comma-delimited values from a table, they typically use a cursor or temp table (as above) to loop through the records. However, if you do not need to use a GROUP BY or an ORDER BY, then you can use the method below that operates in batch to handle the task. This cannot be used with GROUP BY DISTINCT, or ORDER BY, because of how SQL Server handles those operations.
Basically, this takes a given variable, and for every row in the table it adds the current value to the variable along with a comma.
declare @vrs varchar(4000)
declare @sTbl sysname
set @sTbl = 'TableName'
set @vrs = ''
select @vrs = @vrs + ', ' + name from syscolumns where id = (select st.id from sysobjects as st where name = @sTbl) order by colorder
set @vrs = right(@vrs, len(@vrs)-2)
print @vrs
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.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1339242_mem1,00.html
Arrao4u 