This article is about how we can change the cursor in the while loop in SQL Server. It's very useful in improving the performance of stored procedures.

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.