Cursors are frowned upon, but I have found times when performance of update query sets are too slow. I believe this happens when the recovery model is full.
The T-SQL below creates a cursor on a table and then steps through each record and updates several fields. This ended up taking about 4 minutes on an 18k record database, which is small, but would take 20 minutes just to update 200 records in the same table with an update query.
ALTER procedure [dbo].[sp_updateDemoData] as
declare @mallId nchar(6)
declare @RowNum int
declare @showme intset @showme = 0
declare malllist cursor for
select mallcode from mall_exp_trend_2010 where pop_20 > 0
OPEN malllist
FETCH NEXT FROM malllist
INTO @mallId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
set @showme = @showme + 1Update e set
e.NUMBER_OF_HOUSEHOLDS_10 = ns.HH_10, e.NUMBER_OF_HOUSEHOLDS_20 = ns.HH_20, e.NUMBER_OF_HOUSEHOLDS_5 = ns.HH_5,
e.AVREGE_HOUSEHOLD_INC_10 = ns.INC_10, e.AVREGE_HOUSEHOLD_INC_20 = ns.INC_20, e.AVREGE_HOUSEHOLD_INC_5 = ns.INC_5,
e.MEDIAN_AGE_10 = ns.AGE_10, e.MEDIAN_AGE_20 = ns.AGE_20, e.MEDIAN_AGE_5 = ns.AGE_5,
e.POPULATION_10 = ns.POP_10, e.POPULATION_20 = ns.POP_20, POPULATION_5 = ns.POP_5
from mallsysNetT1.dbo._mall e, mall_exp_trend_2010 ns
where ns.MALLCODE = e.MALLCODE and e.MALLCODE = @mallId;if @showme = 100
BEGIN
print ‘processed records: ‘ + cast(@RowNum as char(6)) — + ‘ ‘ + @mallId
set @showme = 0
ENDFETCH NEXT FROM malllist
INTO @mallId
END
CLOSE malllist
DEALLOCATE malllist