CODEWITHSUNDEEP
sqlsql-servert-sqlloopscursor
user48408
user48408

Reputation: 3354

Sql cursor loop taking a minute to loop through 20 rows

The code from the loop below is taking up to a minute to complete. I've removed all processing /calculations in the body of the loop to try and pinpoint what is taking so long and so it appears just iterating over the rows (only 20) is what is taking time. Would re-writing it as a while loop increase performance or is there something inherently wrong with the code below?

declare tl_cursor cursor local forward_only dynamic optimistic
for 
select EId, Date, Hrs, Code1, Code2,  TLId, Acc
from TLines where Account < 0 and Date=@magicdate
for update of Cost, Charge, Acc, RowId, ParentTLId

open tl_cursor
fetch next from tl_cursor
into @tl_eid, @tl_date, @tl_hrs, @tl_account, @tl_tlid

while @@fetch_status = 0
begin
   --Removed all calculations in there to narrow down bottleneck
NextRecord:
    update TLines set Acc = 0 where current of tl_cursor

    fetch next from tl_cursor
    into @tl_eid, @tl_date, @tl_hrs, @tl_account, @tl_timelineid
end

close tl_cursor
deallocate tl_cursor

Upvotes: 0

Views: 402

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

This would probably be faster:

UPDATE  TLines
SET     Acc = 0
WHERE   Account < 0 
  and   Date=@magicdate

If you want to read more about why and how to avoid Cursors in T-Sql, check out this article: http://www.sqlservercentral.com/articles/T-SQL/66097/. Sadly, the notoriously opinionated author never finished the series, but the first two articles should help get you started.

Upvotes: 3

Twinkles
Twinkles

Reputation: 1994

Isn't that identical to:

UPDATE TLines 
SET Acc = 0
WHERE Account < 0 AND Date=@magicdate

Upvotes: 7

Related Questions