Performance problem using cursor

Transact-SQL
1

Hello,
I have the following code that takes some 8 minutes to run - and it is too long. Is there a way to shorten this time?
Please find the code below:
thx

declare @Arg_UID_pr char(20)
declare @Arg_ID_pr char(20)
declare @Arg_ID_cartera DECIMAL(10,0)
declare @Arg_fecha_tran DATE
declare @Arg_divisa NCHAR(3)
declare @Arg_Total_div_loc money
declare @Arg_Total_div_car money
declare @Arg_divisa_codigo char(20)

declare cur CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY for
select Uid_producto, id_producto, ID_cartera, fecha_tran, divisa, total_div_loc, total_div_car, (Case divisa when 'EUR' THEN 'FX_NEUTRO' when 'US$' THEN 'USDEUR:CUR' when 'GB£' THEN 'GBPEUR:IND' when 'CHF' THEN 'CHFEUR:IND' ELSE divisa END) from gestor_transac

open cur

fetch next from cur into @Arg_UID_pr, @Arg_ID_pr, @Arg_ID_cartera, @Arg_fecha_tran, @Arg_divisa, @Arg_Total_div_loc, @Arg_Total_div_car, @Arg_divisa_codigo

while @@FETCH_STATUS = 0 BEGIN

exec gestor_transac_proced @Arg_UID_pr, @Arg_ID_pr, @Arg_ID_cartera, @Arg_fecha_tran, @Arg_divisa, @Arg_Total_div_loc, @Arg_Total_div_car, @Arg_divisa_codigo

fetch next from cur into @Arg_UID_pr, @Arg_ID_pr, @Arg_ID_cartera, @Arg_fecha_tran, @Arg_divisa, @Arg_Total_div_loc, @Arg_Total_div_car, @Arg_divisa_codigo

END

close cur
deallocate cur

2

What does the procedure do? How many rows in the cursor? My suggestion would be to do whatever the procedure does on all the rows at once in a set base fashion.

3

I would look at two things.

First, make the cursor read-only or fast forward. I can't recall the syntax but it's easy to look up. That way it will only run the outer query once.

Second, make the stored procedure faster. Without knowing what it's doing it's hard to suggest fixes.

Third, can you eliminate the cursor and replace with with a series of updates that operate on all the rows of the source table. But we'd need to see the procedure to help with that.

Ok, three :slight_smile: