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.