March 11, 2005 at 8:46 am
HI ,
It's rather so old an issue about cursor's slowing down the performance. But How slow that can be..!?.
Recently i had to update a coulmn in the table/database ( around a lakh/lac records) and i had to prefix the column with 5 leadign zeros and update it.
So i could say " Update tab1 set col1 = '00000'+ col1 where col1 is not null and col1 <>' '. This works faster as we know it is direct update statement on the table.
But i wanted to see the performance via a cursor as it is just a plain update without any comlpex calculations or joins ..
so i created a cursor and fetched the col1 value in to a variable and
then said
fetch ...
While (FETACH_STATUS=0)
BEGIN
Set @var1 = '00000' + @var1
update set tab1 set col1 = @var1
FETCH .. again
END
Close..
Deallocate
This was taking a phenominal time ..like 500 rows/min.. which is like 200 mins to update a lack records .. i mean this is so unacceptable...
Is this usual .. or ami making a mistake anywhere... ( of course i have tried
fast_forward, readonly, static etc )
Please let me know.. .coz i have seen some stored procs using cursors in our system and they are not that bad at all. i mean not this bad.. so just wanted to know..
Thanks
THINQDigital
March 13, 2005 at 11:29 pm
I suppose you had a WHERE clause on that update ?
Also, you can choose between using the primary key, or "Where current of @cursor".
I think "cursors are bad for performance" is like "eat less red meat" - true for 99% of people, but you should understand what is really happening to make an informed choice.
Its not so much cursors thats slow, its the way execution is 'optimised'. When you have an UPDATE with a unique key condition in a cursor loop, that Update is optimised for affecting just 1 row. The optimiser will not consider that the cursor loop will end up affecting every row. The fastest way to update all rows is NOT the same as repeating the fastest way to update 1 row - which may be surprising.
March 14, 2005 at 1:34 pm
hi I am Attaching the SQL here FYI,
DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'CSFB_POUpdate'
BEGIN TRANSACTION @TranName
declare @ponum varchar(50),
@serail_num varchar(50),
@msg1 varchar(50)
declare cur_ponum cursor for
select serien_nr,C_PO_Number from vw.komp where serien_nr in (select [Serial Number]from Batch2)
open cur_ponum
fetch next from cur_ponum into @serail_num,@ponum
WHILE @@FETCH_STATUS = 0
BEGIN
select @msg1= '00000' + @ponum
update vw.komp set C_PO_Number =@msg1 where serien_nr =@serail_num
fetch next from cur_ponum into @serail_num,@ponum
END
close cur_ponum
deallocate cur_ponum
commit transaction CSFB_POUpdate
Thanks for your time
THNQdigital
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply