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. 😉