22nd Jun 2009
How to Convert Cursor into While Loop In Sqlserver ?
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.
Hey really nice article.
Nice and practically needed functionality.
Nice and helpful article.
Really a good article.
Thanks for sharing the knowledge & such a nice article.
Hope you will continue this in future as well.
Hey Kuldip this is really nice article…
Thanks for sharing information.
Regards
Jigar
Nice article kuldip,
Thanks for sharing
I have just implement the same approach but my case is little different.
I don’t want to delete records from temp table so I have used below approach.
DECLARE @iNextCalID BIGINT, @iCurrCalID BIGINT
SET @iCurrCalID = 0;
WHILE(1 = 1)
BEGIN
SELECT
@iNextCalID = MIN(CalendarID)
FROM
#tmpCalendar
WHERE
CalendarID > @iCurrCalID;
IF @iNextCalID is NULL
BREAK;
SET @iCurrCalID = @iNextCalID;
END
—————————————————-
Here what I have done is,
Instead of deleting the record from temp table get MIN(Primary Key ID) where Primary Key ID is Grater Than (>) current ID and assign it to Next ID..
If Next ID is NULL then BREAK the LOOP…
This approach is also good and replacable of CURSOR if you want to keep your TEMP table as it is…
it’s nice thing it’s working in some codition not in every condition it’s work.
one thing is you need Primary key column in your temp table for this thing
you are not able do without primary key column, so if you want to work on varchar
then it’s not work.
If you remove from the #temp table then that good approch because it has confirm that
no records will repeat.
another thing you are not able to do on the forgien key tables because it always
have more than one entry in the table.
Yes certainly a nice aproach..
Really u saved my time and efforts , thanks alot