Search code examples
sqlsql-servercursor

Improve performance by removing cursor

This script is taking hours to execute, is there any way to remove cursor and still iterate over the table on primary key responseId ?

DECLARE ResponseCursor CURSOR
FOR
     SELECT responseId FROM ResponseTable

FETCH NEXT FROM ResponseCursor INTO @ResponseId

WHILE @@fetch_Status =0
BEGIN
    --Insert Logic 
    FETCH NEXT FROM ResponseCursor INTO @ResponseId
END

CLOSE ResponseCursor
DEALLOCATE ResponseCursor

Thanks.

Solution

  • Actually there are many select statements in insert logic.

    So i removed Cursor and inserted using joins that table has 100 columns and nearly 1 lakh rows and it took 2 mins to insert all data.

    with cursors it took 18 hours,so using joins its super fast.