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.
Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated sub query or derived tables, if you need to perform row-by-row operations.
Do not forget to close SQL Server cursor when its result set is not needed.
To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.
Do not forget to de allocate SQL Server cursor when the data structures comprising the cursor are not needed.
To de allocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.
Try to reduce the number of records to process in the cursor.
To reduce the cursor result set, use the WHERE clause in the cursor’s select statement. It can increase cursor performance and reduce SQL Server overhead.
Try to reduce the number of columns to process in the cursor.
Include in the cursor’s select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.
Use READ ONLY cursors, whenever possible, instead of updatable cursors.
Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.
Try avoid using insensitive, static and key set cursors, whenever possible.
These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.
Use FAST_FORWARD cursors, whenever possible.
The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.
Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.
If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.