SQL Server Performance Forum – Threads Archive

Needed Advice on cursors

Hi guys, currently i did a SP which is around 550lines. The purpose of the SP is to scan thru every single record(subscriber details around 10 fields) in a table ( around 1 million records) and determine whether individual subscriber is eligible for certain bonuses. The SP is working fine, but its far too slow. The cursor i used is fast-forward read only cursor. i’ve tried the while loop which don’t use cursors, the result is worse than using a cursor. I believe i’ve gone wrong somewhere, and needed some guidance.. Please advice..

---

there are some alternatives to cursors
Use WHILE LOOPS
Use temp tables
Use derived tables
Use correlated sub-queries
Use the CASE statement
Perform multiple queries
more on
http://www.sql-server-performance.com/cursors.asp —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

http://www.sqlteam.com/item.asp?ItemID=5761
in addition to above reference. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Cursors are Evil. Dont use them. 99% of the time, cursors can be replaced with set based logic. If you can post the significant part of your code, we will help you rewrite it. Roji. P. Thomas
http://toponewithties.blogspot.com

---

yes, cursors should be last option for you.
better if u can post ur code —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

Thank you guys for the prompt reply.. the SP is around 550 lines, is it ok to post it here?? I’ve yet to clean up the codes, as i’m pretty busy recently. I managed to get the result that i wanted but the processing time is far to long, despite running on 2 dual core xeon server with 4GB Ram.. As this SP got around 3 hrs of time allowance to process daily. hope i can get it optimized.

---

How about database optimization jobs schedule in this case? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Hi guys, I’ve actually cut out one part of the SP to test how fast it works.
It is doing very simple low balance checking and updating of the DB. but it turn out to be really slow too. I’ve tried to make it run without cursor, but it turns out to be running slower than cursor. Need some advice on this.. thanks.. CREATE PROCEDURE [dbo].[LowBalanceCounterTrackKeeper] AS
SET NOCOUNT ON
DECLARE @acc_id nvarchar (20)
DECLARE @service_class int
DECLARE @acc_balance decimal
DECLARE @BalanceCounterExist int DECLARE @CounterBalanceThreshold decimal [email protected] int,
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int
DECLARE BalanceCounter_cursor cursor fast_forward read_only for SELECT acc_id, service_class, acc_balance FROM Sub_Info OPEN BalanceCounter_cursor
FETCH NEXT FROM BalanceCounter_cursor
INTO @acc_id, @service_class, @acc_balance WHILE @@FETCH_STATUS = 0
BEGIN set @BalanceCounterExist = 0 SELECT @CounterBalanceThreshold = cast ( CounterBalanceThreshold as decimal) from Service_Class
where service_class = @service_class
— ******************************* This Portion helps to keep track of the low balance counter ************************************************************************ IF(@acc_balance <= @CounterBalanceThreshold )
BEGIN IF EXISTS (SELECT * FROM LowBalanceCounter where msisdn = @acc_id)
BEGIN
SET @BalanceCounterExist = 1
END IF (@BalanceCounterExist = ‘0’)
BEGIN
insert into LowBalanceCounter values (@acc_id , 1) END
ELSE
BEGIN
UPDATE LowBalanceCounter
SET Threshold_Counter = Threshold_Counter +1
WHERE msisdn = @acc_id
END
END
ELSE
BEGIN
IF (@BalanceCounterExist >= ‘0’)
BEGIN
DELETE FROM LowBalanceCounter
WHERE msisdn = @acc_id
END
END
FETCH NEXT FROM BalanceCounter_cursor
INTO @acc_id, @service_class, @acc_balance
END
CLOSE BalanceCounter_cursor
DEALLOCATE BalanceCounter_cursor
GO

---

Try the following… SELECT i.acc_id, i.service_class, i.acc_balance,
CounterBalanceThreshold = cast ( c.CounterBalanceThreshold as decimal)
into #temp
FROM Sub_Info i
join Service_Class c on c.service_class = i.service_class
where i.acc_balance < cast ( c.CounterBalanceThreshold as decimal) insert into LowBalanceCounter
select t.acc_id, 1 from #temp t
where not exists(select 1 LowBalanceCounter l where l.msisdn = t.acc_id ) UPDATE l
SET Threshold_Counter = Threshold_Counter +1
LowBalanceCounter l
join #temp t on t.acc_id = l.msisdn DELETE l FROM LowBalanceCounter l
join #temp t on t.acc_id = l.msisdn
Mohammed U.

---

Hi MohammedU, The code works very well, thanks.. btw i’ve got some enquiry. Have you have any experience in doig a SP for following scenario. bonuses are given to subscriber, there are 3 bonus A, B and C . Each subscriber is only entitled to one bonus. so if a subscriber is eligible to bonus A, B and C. he is only given bonus A (highest priority). If a subscriber is eligible to Bonus B and C, he is only given bonus B (second highest priority) I did this SP with IF Else Loop, which i find very inefficient. mind to give some advice?? regards
Jimmy

---

Hi Guys, I have got another question, currently my SP is able to compute 650000 records in like 12 min.. but i increase the record to 2milion, it takes like a lifetime to complete. why is that so??

---

Change the MohammedU’s "SELECT … INTO #temp FROM …" syntax to this: CREATE TABLE #temp (col1 ………..) INSERT INTO #temp (col1, ….)
SELECT ………
FROM …….. The original query probably causes locking issues. Also try doing this in batches of 1000 records – easiest probably would be to add an identity column on the temp table, and check how many rows are in it. DECLARE @test INT, @Runner INT Immediately after the INSERT INTO statement, you do
SET @Test = @@ROWCOUNT SET @Runner = 0
WHILE @Runner <= @test
BEGIN
……………………
SET @Runner = @Runner + 1000
END In the WHILE loop, you do whatever you need to do with a TOP 1000 clause, and with <identity_col> > @Runner in the WHERE statement, and with ORDER BY <identity_col>.

---

Hi guys, below is the SP that i have written. when it is used to handle 2million records, it sometimes complete at around 45mins, but sometimes it uses like a lifetime. which i have no idea why is it performing that. hopefully i could some help from you guys. thanks in advance.
regards
Jimmy
DECLARE Sub_Info_cursor cursor fast_forward read_only for
SELECT acc_id, tmp_block, service_class, lang, acc_balance, supervision_period_expiry_date FROM sub_info OPEN Sub_Info_cursor
FETCH NEXT FROM Sub_Info_cursor
INTO @acc_id, @tmp_block, @service_class, @lang, @acc_balance, @supervision_period_expiry_date WHILE @@FETCH_STATUS = 0
BEGIN set @NotificationMsg = ”
set @ServicePeriodMsg = ”
set @LowBalanceMsg = ”
set @LowBalance = 0
SET @NotificationExist1 = 0
SET @NotificationExist2 =0
SET @PromotionExist = 0
SET @BalanceCounterExist =0 SELECT @CounterBalanceThreshold = cast ( CounterBalanceThreshold as decimal) ,
@ExpiryBalanceThreshold = cast ( ExpiryBalanceThreshold as decimal) ,
@Threshold_Counter= cast ( Threshold_Counter as int) ,
@NoBonusFlag = cast ( NoExpiryBonusFlag as bit) ,
@SPstartdate = DATEADD (day , cast ( PromotionStart as int) , @supervision_period_expiry_date) ,
@SPenddate = DATEADD (day , cast ( PromotionEnd as int) , @supervision_period_expiry_date ),
@NotificationPriority =cast (NotificationPriority as int),
@ServicePeriodPriority =cast (ServicePeriodPriority as int),
@LowBalancePeriodPriority =cast (LowBalancePriority as int),
@n1 =cast (n1 as int),
@n2 = cast (n2 as int),
@ServicePeriodBalanceThreshold = cast (ServicePeriodBalanceThreshold as decimal) from Service_Class
where service_class = @service_class
— ########## Check if today falls between the promotion date #####################
IF getdate() >= @SPstartdate AND getdate() <= @SPenddate
BEGIN
set @ServicePeriodPromotion = 1
END
ELSE
BEGIN
set @ServicePeriodPromotion = 0
END
–################################################################# if (@tmp_block =’0′)
begin
IF EXISTS (SELECT 1 FROM Notification_1 where msisdn = @acc_id)
BEGIN
SET @NotificationExist1 = 1
END IF EXISTS (SELECT 1 FROM Notification_2 where msisdn = @acc_id)
BEGIN
SET @NotificationExist2 = 1
END IF EXISTS (SELECT 1 FROM Notification_Promotion where msisdn = @acc_id)
BEGIN
SET @PromotionExist = 1
END IF EXISTS (SELECT 1 FROM LowBalanceCounter where msisdn = @acc_id)
BEGIN
SET @BalanceCounterExist = 1
END IF EXISTS (SELECT 1 FROM Notification_counter where msisdn = @acc_id)
BEGIN
SET @LowBalance = 1
END IF (@NotificationExist1 = ‘0’ AND @n1 < ’99’ ) AND (@ExpiryBalanceThreshold >= @acc_balance) AND (DATEDIFF(day, @[email protected], getdate()) = 0)
BEGIN Select @BonusPercent=BT.BonusValue, @BonusType = BT.BonusType, @HourToRefill = cast (ToR.Refill_Time as nvarchar(20))
From BonusType BT, Time_of_Refill ToR
Join Bonus_Evaluation BE ON (service_class = @service_class AND PromotionType = ‘Expiry’)
Where BT.Bonus = BE.Bonus_Type and ToR.Refill_Tier = BE.Refill_Time_Delta IF(@BonusType = ‘Dollar’)
BEGIN
SET @BonusPercent = ‘$’ + @BonusPercent
END
ELSE
IF(@BonusType = ‘Percent’)
BEGIN
SET @BonusPercent = @BonusPercent + ‘%’
END
IF @lang = @EnglishLanguage
BEGIN
IF @NoBonusFlag = 1
BEGIN
IF @n1>0 — account already expired
BEGIN
set @NotificationMsg = @SMS_Msg_YourAccount + @SMS_Msg_HasExpiredFor + cast ( @n1 as varchar(2)) + @SMS_Msg_Days
END IF @n1=0 — account will expire Today
BEGIN
set @NotificationMsg = @SMS_Msg_YourAccount + @SMS_Msg_IsExpiringToday END IF @n1<0 — account is expiring soon
BEGIN
set @NotificationMsg = @SMS_Msg_YourAccount + @SMS_Msg_IsExpiringIn + cast((@n1*-1) as varchar(2)) + @SMS_Msg_Days END
END
ELSE
IF @NoBonusFlag =0
BEGIN
IF @n1>0 — account already expired
BEGIN
set @NotificationMsg = @SMS_Msg_YourAccount + @SMS_Msg_HasExpiredFor + cast ( @n1 as varchar(2)) + @SMS_Msg_Days
+ @SMS_Msg_PleaseTopUpBy + Cast(Cast( Dateadd( hh, @HourToRefill, getdate()) as smalldatetime) as nvarchar)
+ @SMS_Msg_AndYouWillHave + @BonusPercent + @SMS_Msg_Bonus
END IF @n1=0 — account will expire Today
BEGIN
set @NotificationMsg = @SMS_Msg_YourAccount + @SMS_Msg_IsExpiringToday
+ @SMS_Msg_PleaseTopUpBy + Cast(Cast( Dateadd( hh, @HourToRefill, getdate()) as smalldatetime) as nvarchar)
+ @SMS_Msg_AndYouWillHave + @BonusPercent + @SMS_Msg_Bonus
END IF @n1<0 — account is expiring soon
BEGIN
set @NotificationMsg = @SMS_Msg_YourAccount + @SMS_Msg_IsExpiringIn + cast((@n1*-1) as varchar(2)) + @SMS_Msg_Days
+ @SMS_Msg_PleaseTopUpBy + Cast(Cast( Dateadd( hh, @HourToRefill, getdate()) as smalldatetime) as nvarchar)
+ @SMS_Msg_AndYouWillHave + @BonusPercent + @SMS_Msg_Bonus
END
END
END
ELSE IF @lang = @IndoLanguage
BEGIN
IF @NoBonusFlag = 1
BEGIN IF @n1>0 — account already expired
BEGIN
set @NotificationMsg = @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_HasExpiredFor + cast ( @n1 as varchar(2)) + @IndoSMS_Msg_Days END IF @n1=0 — account will expire Today
BEGIN
set @NotificationMsg = @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_IsExpiringToday END IF @n1<0 — account is expiring soon
BEGIN
set @NotificationMsg = @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_IsExpiringIn + cast((@n1*-1) as varchar(2)) + @IndoSMS_Msg_Days END
END
ELSE
IF @NoBonusFlag =0
BEGIN IF @n1>0 — account already expired
BEGIN
set @NotificationMsg = @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_HasExpiredFor + cast ( @n1 as varchar(2)) + @IndoSMS_Msg_Days
+ @IndoSMS_Msg_PleaseTopUpBy + Cast(Cast( Dateadd( hh, @HourToRefill, getdate()) as smalldatetime) as nvarchar)
+ @IndoSMS_Msg_AndYouWillHave + @BonusPercent + @IndoSMS_Msg_Bonus END IF @n1=0 — account will expire Today
BEGIN
set @NotificationMsg = @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_IsExpiringToday
+ @IndoSMS_Msg_PleaseTopUpBy + Cast(Cast( Dateadd( hh, @HourToRefill, getdate()) as smalldatetime) as nvarchar)
+ @IndoSMS_Msg_AndYouWillHave + @BonusPercent + @IndoSMS_Msg_Bonus END IF @n1<0 — account is expiring soon
BEGINset @NotificationMsg = @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_IsExpiringIn + cast((@n1*-1) as varchar(2)) + @IndoSMS_Msg_Days
+ @IndoSMS_Msg_PleaseTopUpBy + Cast(Cast( Dateadd( hh, @HourToRefill, getdate()) as smalldatetime) as nvarchar)
+ @IndoSMS_Msg_AndYouWillHave + @BonusPercent + @IndoSMS_Msg_Bonus END
END
END
——————————————————————————————————————–
END
ELSE IF (@NotificationExist2 = ‘0’ AND @n2 <’99’ ) AND (@ExpiryBalanceThreshold >= @acc_balance) AND (DATEDIFF(day, @[email protected], getdate()) = 0) AND (@NotificationExist1 > ‘0’)
BEGIN
IF( @n1 < 0)
BEGIN
SET @n1 = 0
END Select @BonusPercent=BT.BonusValue, @BonusType = BT.BonusType, @HourToRefill = cast (ToR.Refill_Time as nvarchar(20))
From BonusType BT, Time_of_Refill ToR
Join Bonus_Evaluation BE ON (service_class = @service_class AND PromotionType = ‘Expiry’)
Where BT.Bonus = BE.Bonus_Type and ToR.Refill_Tier = BE.Refill_Time_Delta IF(@BonusType = ‘Dollar’) BEGIN SET @BonusPercent = ‘$’ + @BonusPercent
END
ELSE
IF(@BonusType = ‘Percent’)
BEGIN
SET @BonusPercent = @BonusPercent + ‘%’
END IF @lang = @EnglishLanguage
BEGIN
SET @NotificationMsg = @SecondReminder [email protected]_Msg_YourAccount + @SMS_Msg_HasExpiredFor + cast ( @[email protected] as varchar(10)) + @SMS_Msg_Days END
ELSE IF @lang = @IndoLanguage
BEGIN
SET @NotificationMsg = @IndoSecondReminder+ @IndoSMS_Msg_YourAccount + @IndoSMS_Msg_HasExpiredFor + cast ( @[email protected] as varchar(2)) + @IndoSMS_Msg_Days END END
–======================================================== Service Period Promotion SMS Notificatrion====================================================================
IF (@PromotionExist = ‘0’) AND (@ServicePeriodBalanceThreshold > = @acc_balance) AND (@ServicePeriodPromotion = 1)
BEGIN Select @BonusPercent=BT.BonusValue, @BonusType = BT.BonusType
From BonusType BT
Join Bonus_Evaluation BE ON (service_class = @service_class AND PromotionType = ‘LowBalanceCounter’)
Where BT.Bonus = BE.Bonus_Type IF(@BonusType = ‘Dollar’)
BEGIN
SET @BonusPercent = ‘$’ + @BonusPercent
END
ELSE
IF(@BonusType = ‘Percent’)
BEGIN
SET @BonusPercent = @BonusPercent + ‘%’
END
IF @lang = @EnglishLanguage
BEGIN
set @ServicePeriodMsg = ‘Promotion : Top up by ‘ + cast( @SPenddate as varchar(165)) + @SMS_Msg_AndYouWillHave + @BonusPercent + @SMS_Msg_Bonus END
ELSE IF @lang = @IndoLanguage
BEGIN
set @ServicePeriodMsg = ‘ Indonesia Promotion : Top up by ‘ + cast( @SPenddate as varchar(165)) + @IndoSMS_Msg_AndYouWillHave + @BonusPercent + @IndoSMS_Msg_Bonus END
END –============================================================================================================================================================================ –########################################## Low Balance Threshold Counter SMS Notification ######################################################################## IF (@BalanceCounterExist =1) AND (@LowBalance = 0) AND ((SELECT Threshold_Counter FROM LowBalanceCounter WHERE msisdn = @acc_id) >= @Threshold_Counter)
BEGIN
Select @BonusPercent=BT.BonusValue, @BonusType = BT.BonusType
From BonusType BT
Join Bonus_Evaluation BE ON (service_class = @service_class AND PromotionType = ‘LowBalanceCounter’)
Where BT.Bonus = BE.Bonus_Type
IF(@BonusType = ‘Dollar’)
BEGIN
SET @BonusPercent = ‘$’ + @BonusPercent
END
ELSE
IF(@BonusType = ‘Percent’)
BEGIN
SET @BonusPercent = @BonusPercent + ‘%’
END
IF @lang = @EnglishLanguage
BEGIN set @LowBalanceMsg = @LowBalanceMsg [email protected]_Msg_YourAccount + @SMS_Msg_IsBelow + cast( @CounterBalanceThreshold as varchar(20)) +’ top up now, ‘
+ @SMS_Msg_AndYouWillHave + @BonusPercent + @SMS_Msg_Bonus END
ELSE IF @lang = @IndoLanguage
BEGIN
set @LowBalanceMsg = @LowBalanceMsg [email protected]_Msg_YourAccount + @IndoSMS_Msg_IsBelow + cast( @CounterBalanceThreshold as varchar(20)) +’ INDO :top up now, ‘
+ @IndoSMS_Msg_AndYouWillHave + @BonusPercent + @IndoSMS_Msg_Bonus END END
–##############################################################################################################################################
IF(@NotificationPriority =’1′ AND @NotificationMsg <> ”)
BEGIN
IF @NotificationExist1 = ‘0’
BEGIN
insert into TSMS values (@acc_id , @NotificationMsg)
insert into Notification_1 values (@acc_id , @NotificationMsg, getdate(), @lang) END
ELSE
IF @NotificationExist2 = ‘0’
BEGIN
insert into TSMS values (@acc_id , @NotificationMsg)
insert into Notification_2 values (@acc_id , @NotificationMsg, getdate(), @lang)
END
END
ELSE IF(@ServicePeriodPriority =’1′ AND @ServicePeriodMsg <>”)
BEGIN
insert into TSMS values (@acc_id , @ServicePeriodMsg)
insert into Notification_Promotion values (@acc_id , @ServicePeriodMsg, getdate(), @lang)
END
ELSE IF(@LowBalancePeriodPriority =’1′ AND @LowBalanceMsg <>”)
BEGIN
insert into TSMS values (@acc_id , @LowBalanceMsg)
insert into Notification_Counter values (@acc_id , @LowBalanceMsg, getdate(), @lang)
END
ELSE IF(@NotificationPriority =’2′ AND @NotificationMsg <> ”)
BEGIN
IF @NotificationExist1 = ‘0’
BEGIN
insert into TSMS values (@acc_id , @NotificationMsg)
insert into Notification_1 values (@acc_id , @NotificationMsg, getdate(), @lang)
END
ELSE
IF @NotificationExist2 = ‘0’
BEGIN
insert into TSMS values (@acc_id , @NotificationMsg)
insert into Notification_2 values (@acc_id , @NotificationMsg, getdate(), @lang)
END
END
ELSE IF(@ServicePeriodPriority =’2′ AND @ServicePeriodMsg <>”)
BEGIN
insert into TSMS values (@acc_id , @ServicePeriodMsg)
insert into Notification_Promotion values (@acc_id , @ServicePeriodMsg, getdate(), @lang)
END
ELSE IF(@LowBalancePeriodPriority =’2′ AND @LowBalanceMsg <>”)
BEGIN
insert into TSMS values (@acc_id , @LowBalanceMsg)
insert into Notification_Counter values (@acc_id , @LowBalanceMsg, getdate(), @lang)
END
ELSE IF(@NotificationPriority =’3′ AND @NotificationMsg <> ”)
BEGIN
IF @NotificationExist1 = ‘0’
BEGIN
insert into TSMS values (@acc_id , @NotificationMsg)
insert into Notification_1 values (@acc_id , @NotificationMsg, getdate(), @lang)
END
ELSE
IF @NotificationExist2 = ‘0’
BEGIN
insert into TSMS values (@acc_id , @NotificationMsg)
insert into Notification_2 values (@acc_id , @NotificationMsg, getdate(), @lang)
END
END
ELSE IF(@ServicePeriodPriority =’3′ AND @ServicePeriodMsg <>”)
BEGIN
insert into TSMS values (@acc_id , @ServicePeriodMsg)
insert into Notification_Promotion values (@acc_id , @ServicePeriodMsg, getdate(), @lang)
END
ELSE IF(@LowBalancePeriodPriority =’3′ AND @LowBalanceMsg <>”)
BEGIN
insert into TSMS values (@acc_id , @LowBalanceMsg)
insert into Notification_Counter values (@acc_id , @LowBalanceMsg, getdate(), @lang)
END END — tmp block End FETCH NEXT FROM Sub_Info_cursor
INTO @acc_id, @tmp_block, @service_class , @lang, @acc_balance, @supervision_period_expiry_date
END
CLOSE Sub_Info_cursor
DEALLOCATE Sub_Info_cursor
GO

---

]]>