Today I was writing a stored procedure and had to use a lot of cursor to accomplish the task, then I got a doubt does extensive use of cursor affect the performance to the stored procedure?. Then I started to search the internet and the following is the result
What is a cursor?
A cursor is a set of rows together with a pointer that identifies a current row.
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in visual basic.
A simple example of a cursor
DECLARE @OrderID INT
DECLARE cursor_getOrderID CURSOR
SET cursor_getOrderID = CURSOR FOR SELECT OrderID FROM Orders
OPEN cursor_getOrderID
FETCH NEXT FROM cursor_getOrderID INTO @OrderID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @OrderID
FETCH NEXT FROM cursor_getOrderID INTO @OrderID
END
CLOSE cursor_getOrderID
DEALLOCATE cursor_getOrderID
Using Cursors affect Performance?
Cursors can affect performance if you use them to keep locks on data.
Some alternatives to using a cursor:
Use WHILE LOOPS
Use temp tables
Use derived tables
Use correlated sub-queries
Use the CASE statement
Perform multiple queries
