Improving SQL Server cursor performance

Hi everyone! Today I want to talk about a performance problem I had a few days ago using a SQL Server cursor.

Imagine using a cursor in the following classic way:

DECLARE @ID INT
DECLARE @ITEM_CODE VARCHAR(50)

DECLARE MY_CURSOR CURSOR FOR 
SELECT ID, ITEM_CODE
FROM MY_TABLE T
LEFT JOIN A_TABLE T1 ON T.ID = T1.ID
LEFT JOIN A_TABLE2 T2 ON T.ID = T2.ID
WHERE MY_CONDITION = 0 

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR INTO @ID, @ITEM_CODE

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @ID
  PRINT @ITEM_CODE

  FETCH NEXT FROM MY_CURSOR INTO @ID, @ITEM_CODE
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Seems incredible, but the first FETCH required about 20 seconds to complete without a valid reason…

Finally, I found a solution adding the attributes READ_ONLY, LOCAL, and FORWARD_ONLY to the cursor declaration:

DECLARE MY_CURSOR CURSOR READ_ONLY LOCAL FORWARD_ONLY FOR 
  SELECT ID, ITEM_CODE 
  FROM MY_TABLE T 
  LEFT JOIN A_TABLE T1 ON T.ID = T1.ID 
  LEFT JOIN A_TABLE2 T2 ON T.ID = T2.ID 
  WHERE MY_CONDITION = 0

After this change the time has dropped to around zero seconds.

Clearly, the READ_ONLY and FORWARD_ONLY attributes are applicable only if no changes are to be made to the underlying data and if it is sufficient to read the cursor only forwards.

I suggest you to consult Microsoft books online for more information about those attirbutes. 😉

Related Posts