I personally face this issues in many Store Procedures i.e. Cursor taking too much time to execute. I am sure many of you will be having similar problem.
I have worked on that issue and found following solution. It may not be perfect for cursor optimization but it does work in my case.
– Original Cursor Code —
Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint
declare CUR_MEDICATIONORDERID cursor FOR
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()
OPEN CUR_MEDICATIONORDERID
FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId
WHILE @@FETCH_STATUS =0
BEGIN
Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId
Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”
FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId
END
CLOSE CUR_MEDICATIONORDERID
DEALLOCATE CUR_MEDICATIONORDERID
——————-
– Modified Cursor Code (faster) –
Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint
Declare Table for the fields you need in the cursor.
declare @IDList table (ID Bigint)
Insert into @IDList
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()
while (select count(ID) from @IDList) > 0
begin
select top 1 @fk_MedicationOrderId = ID from @IDList
Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId
Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”
delete from @IDList where ID = @fk_MedicationOrderId
end
——————-
What I have done here is to convert “cursor” into “while loop”. It is just to get your data in any temporary table or table variable after that just delete the rows from that at end of loop.
It helps in improving performance, I have personally tried it in many stored procedures.
Let me know if you find any other useful way of improving performance.