CODEWITHSUNDEEP
sql-server
Love Test
Love Test

Reputation: 9

update using cursor

hope all you are fine. i have some problem in sql server query.

i want to update recorde in a table which have 534,000 rows. if i create a while loop for update all record then it takes time.

then i should create a cursor for update record. after this i create a cursor for update using Fetch. this cursor complete updating process quickly in 10,000 rows but when i use table which have more than 30,000 rows it takes 5 minuts to execute Query. i don't know what is the problem.

its my code

DECLARE @RNo INT --Declaring the Variable @id
DECLARE @id INT --Declaring the Variable @id
set @RNo=1

DECLARE @MyCursor CURSOR -- Declaring the Cursor
SET @MyCursor = CURSOR --Assigning the cursor
FOR
SELECT Col1 FROM MyTable  --Query related to Cursor
for update of Col2

OPEN @MyCursor -- Opening the Created cursor
FETCH NEXT FROM @MyCursor --Retrieving the record one by one
INTO @id --Assigning the value in declared Variable
    WHILE @@FETCH_STATUS = 0 
    BEGIN

        update MyTable
        set Col2=@RNo
         where current of @MyCursor

        set @RNo=@rno+1
        print @id
FETCH NEXT
    FROM @MyCursor INTO @id
END
CLOSE @MyCursor -- Closing the Cursor
DEALLOCATE @MyCursor -- Deallocating the Cursor Memory

Note: My Query execute with correct data but problem is it takes 5 minuts i only want to execute this query within 10 seconds.

Thanks in Advance

Upvotes: 0

Views: 1721

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

A SQL Server expert once told me, if you need to use cursors, ALWAYS make sure you use a LOCAL STATIC cursor. That aside, this query will update your data in one shot.

;WITH TMP AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY Col1) RN
  FROM MyTable
)
UPDATE TMP SET Col2 = RN;

I have arbitrarily chosen to produce the Row Numbering based on ORDER BY Col1, which you can tweak. Your cursor did not have an ORDER BY clause so it would have been pseudo-random anyway.

Upvotes: 3

Related Questions