Introduction
In a relational database, operations are carried out on a complete set of rows in a table. The SELECT statement returns all the rows that satisfy the condition in the WHERE clause of the statement. This complete set of rows that the SELECT statement returns is called a ResultSet.
Some applications cannot work effectively with the entire set as a unit. These applications need a mechanism to work with one row, or a small block of rows at a time. Sometimes multiple processing steps are required on each individual record before moving on to the next record. In these cases, the result set cannot be created by traditional SQL set statements. Now what is the solution in case where we need to access data row-by-row basis?
Microsoft SQL Server answered this question using the concept of Cursors. |
Cursors
Cursors are special programming constructs that allow data to be manipulated on a row-by-row basis, similar to other structured programming languages. They are declared like a variable, and then move one record at a time using a loop for control.Cursors basically works on the concept of LOOPING that is given in almost every programming language.
Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.
In more simplified way, a cursor normally works in the following manner:
|
- Declare a cursor
- Open a cursor
- Fetch the data from the cursor
- You can fetch row by row and make multiple operations on the currently active row in the cursor
- Close the cursor
- Deallocate the cursor to release SQL Server resources
You can use the cursors to do the following:
|
- Allow positioning at specific rows of the resultset.
- Retrieve a single row, or set of rows, from the current positions in the resultset.
- Support data modifications to the rows at the current position in the resultset.
- Support different levels of visibility to changes that others users make to the database data that is presented in the resultset.
- Provide T-SQL statements in scripts, stored procedures, and triggers, to access the data in the resultset.
|
Cursor Syntax – Advanced View
—————————————————————————————————–
Declaring Cursors
Following is the T-SQL Syntax for declaring a cursor:
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
Explanation:
|
- cursor_name – A cursor name can contain maximum of 128 characters.
- LOCAL - Specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. “An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates”. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.
- GLOBAL - Specifies that cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The GLOBAL cursor will be implicitly deallocated at disconnect.
- FORWARD_ONLY - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported. “That means with this type of cursors you can’t fetch the first, previous or last record in the cursor result set, you can only fetch the next record in the resultset available.” If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
- STATIC - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and the modifications made to base tables are not reflected in the data returned by fetches made to this cursor. “That means when cursor is running, if at that time you’ll made any modification in the data of the table that you are using currently in the cursor then the changes made to the data will not reflect in the cursor resultset”.
- KEYSET - Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, is visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.
- DYNAMIC - Specifies that cursor reflects all data changes made to the base tables as you scroll around the cursor. FETCH ABSOLUTE option is not supported with DYNAMIC cursor.
- FAST_FORWARD - Specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.
- READ ONLY - Prevents updates from being made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
- SCROLL_LOCKS - Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.
- OPTIMISTIC - Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
- TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.
- select_statement - select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within a select_statement of a cursor declaration.
- UPDATE [OF column_name [,...n]] - Specifies that all cursor’s columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.
|
Opening a Cursor
Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
Explanation: |
- GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.
- cursor_variable_name - The name of a cursor variable that references a cursor.
After a cursor is open, you can determine the number of rows that were found by the cursor, In other words you can say that how many number of rows your query will return on which you are running the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.
|
Fetching data from Cursor
Now your cursor is declared for a particular result set and you have opened your cursor using OPEN command, now you can fetch data from it row by row and make multiple operations on the currently active row in the cursor. To fetch data from a cursor, you can use the following syntax:
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}
]
FROM
]
{ { [GLOBAL] cursor_name } | @cursor_variable_name}
[INTO @variable_name[,...n] ]
Explanation:
|
- NEXT - The default cursor fetch option. FETCH NEXT returns the next row after the current row.
- PRIOR - Returns the prior row before the current row.
- FIRST - Returns the first row in the cursor.
- LAST - Returns the last row in the cursor.
- ABSOLUTE {n \| @nvar} - If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched.
- RELATIVE {n \| @nvar} - If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row.If n is 0, the same row is fetched again.
- GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.
- cursor_variable_name - The name of a cursor variable that references a cursor.
- INTO @variable_name[,...n] - Allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.
By default, the FETCH NEXT option works.
|
Closing a Cursor
Now after declaring and opening the cursor we used the FETCH command to fetch data from the cursor and perform some action on it, now when you have finished working with a cursor, you must close the cursor to release any resources and locks that SQL Server may have used while the cursor was open. To close a cursor, you can use the following syntax:
CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }
Explanation: |
- GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.
- cursor_variable_name - The name of a cursor variable that references a cursor.
If you have closed a cursor, but have not deallocated it, you can open it again when needed.
|
De-allocating a Cursor
When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor. To deallocate a cursor, you can use the following syntax:
DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}
Explanation: |
- GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.
- cursor_variable_name - The name of a cursor variable that references a cursor.
Whenever using cursors remember to close and deallocate the cursor. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.
|
The four API server cursor types supported by SQL Server are: |
- Static cursors - A static cursor always displays the result set as it was when the cursor was opened. Static cursors are always read-only.
- Dynamic cursors - Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor.
- Forward-only cursors - A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor.
- Keyset-driven cursors - The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened.
|
Why Cursors are Slow
Actually what I observed about cursor is that CURSORS TRAET EVERY ROW AS A SEPRATE RESULT SET. Let’s suppose when we execute two select statements in the Query Analyzer then it will return two result sets in the Result pane. Same in the case of cursors if we are running a cursor to process a result set of 20 rows then it will execute a separate select statement for every row. That’s why if you need to process a resultset of 1000 rows then it will put a huge load on your database server. |
Performance Tuning SQL Server Cursors
If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the tasks that are as follows. |
- Use WHILE LOOPS
- Use temp tables
- Use derived tables
- Use correlated sub-queries
- Use the CASE statement
- Perform multiple queries
More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor. Some techniques that can work as an alternative of cursors are discussed below,
|
- Process less number of records with cursors If you think that you must use a cursor then try to reduce the number of records to process. One way to do this is to move the records that need to be processed into a temp table first, and then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subsets of records to be inserted into the temp table are substantially less than those in the original table. The lower the number of records to process, the faster the cursor will finish.
- If the number of rows you need to return from a query is small, and you need to perform row-by-row operations on them, don’t use a server-side cursor. Instead, consider returning the entire rowset to the client and have the client perform the necessary action on each row, and then return any updated rows to the server.
- If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server. If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.
- Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in tempdb database, which increases overhead and can cause resource contention issues.
- If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be.
- Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency.
- When you are done using a cursor, don’t just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don’t DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.
- If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources.
- If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment.
- When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit.
- If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible.
- If a transaction you have created contains a cursor (try to avoid this if at all possible), ensure that the number of rows being modified by the cursor is small. This is because the modified rows may be locked until the transaction completes or aborts. The greater the number of rows being modified, the greater the locks, and the higher the likelihood of lock contention on the server, hurting performance.
- In SQL Server, there are two options to define the scope of a cursor. LOCAL and GLOBAL keywords in the DECLARE CURSOR statement are used to specify the scope of a cursor. A GLOBAL cursor can be referenced in any stored procedure or batch executed by a connection. LOCAL cursors are more secure as they cannot be referenced outside the procedure or trigger unless they are passed back to the calling procedure or trigger, or by using an output parameter. GLOBAL cursors must be explicitly deallocated or they will be available until the connection is closed. For optimum performance, you should always explicitly deallocate a cursor when you are done using it. LOCAL cursors are implicitly deallocated when the stored procedure, the trigger, or the batch in which they were created terminates. We can use LOCAL cursors for more security and better scope of the cursor in our application, which also helps to reduce resources on the server, boosting performance.
- Consider using asynchronous cursors if you expect your result set to be very large. This allows you to continue processing while the cursor is still being populated. While it may not actually speed up your application, it should give the appearance to your end users that something is happening sooner that if they have to wait until the entire cursor is populated.
- If you have to use a cursor, break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit immediately.
- If you are using the same cursor more than once in a batch of work, (or within more than one stored procedure), then define the cursor as a global cursor by using the GLOBAL keyword. By not closing or deallocating the cursor until the whole process is finished, a fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use.
|
Eliminating Cursors
Now as you can see that there are many server performance related problems with SQL cursors, so we must have to find out any alternative of cursors and there are some possible alternatives are listed above in this article. But if you take my opinion I think WHILE LOOPS are the best way to replace cursors. But in performance point of view it’s obvious that while loops increases the performance in comparison of cursors but it all depends on the requirement of the application and the type and amount of data you need to process. So take a look of WHILE LOOP using the following example.
The following code is done with SQL cursors.
DECLARE @item_category_id INT
DECLARE @order_id INT
DECLARE @purchase_order_id INT
DECLARE item_cursor CURSOR FAST_FORWARD FOR
SELECT it.item_category_id, ord.order_id FROM dbo.item_categories it
INNER JOIN dbo.orders ord ON ord.item_category_id = it.item_category_id
WHERE ord.order_date >= ‘1-sep-05′ AND it.isSuspended != 1
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @item_category_id, @order_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
/*Call other code here to process your purchase order for this item*/
FETCH NEXT FROM item_cursor INTO @item-category_id, @order_id
END
Now take a look of alternative view of performing the cursor type of functionality using WHILE LOOPS.
–Declare variables
DECLARE @item_category_id INT
DECLARE @order_id INT
DECLARE @purchase_order_id INT
–Declare a memory table
DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
–THE IDENTITY STATEMENT IS IMPORTANT!
item_category_id INT,
order_id INT)
–now populate this table with the required item category values
INSERT INTO @item_table
SELECT — Same SELECT statement as that for the CURSOR
it.item_category_id, ord.order_id FROM dbo.item_categories it
INNER JOIN dbo.orders ord ON ord.item_category_id = it.item_category_id
WHERE ord.order_date >= ‘1-sep-05′ and it.isSuspended != 1
DECLARE @item_category_counter INT
DECLARE @loop_counter INT
SET @loop_counter = ISNULL(SELECT COUNT(*) FROM @item_table),0)
– Set the @loop_counter to the total number of rows in the
– memory table
SET @item_category_counter = 1
WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN
SELECT @item_category_id = item_category_id ,@order_id = order_id
FROM @item_table WHERE primary_key = @item_category_counter
–Now pass the item-category_id and order_id to the OUTPUT stored procedure
EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
/*Call other code here to process your pruchase order for this item*/
SET @item_category_counter = @item_category_counter + 1
END
That’s it! You have just converted your CURSOR statement to a typical SQL WHILE loop. In the above query it is noticed that this approach gave a 75% increase in performance over the statement using the CURSOR keyword. But again all depends on you query criteria, but yes it is sure that it will increase your query performance.
In this article I tried my best to put all things related to cursors, but no one is perfect, so if anyone having any interesting or special concepts about Cursors then do comment on this article I’ll include those concepts also in my article.
Thank you,
|
|
*******
|
 |
Reference from and many thanks to following websites and articles:
Microsoft’s Book Online For SQL Server 2000
Sql-Server-Performance
Sql Server Central
|
![clip_image001[4]](http://techahead.files.wordpress.com/2008/04/clip-image0014.gif){kind=small}
![clip_image002[4]](http://techahead.files.wordpress.com/2008/04/clip-image0024.gif){kind=small}
![clip_image003[4]](http://techahead.files.wordpress.com/2008/04/clip-image0034.gif){kind=small}
![clip_image004[4]](http://techahead.files.wordpress.com/2008/04/clip-image0044.gif){kind=small}
![clip_image005[4]](http://techahead.files.wordpress.com/2008/04/clip-image0054.gif){kind=small}
![clip_image006[4]](http://techahead.files.wordpress.com/2008/04/clip-image0064.gif){kind=small}
![clip_image007[4]](http://techahead.files.wordpress.com/2008/04/clip-image0074.gif){kind=small}
![clip_image008[4]](http://techahead.files.wordpress.com/2008/04/clip-image0084.gif){kind=small}
![clip_image009[4]](http://techahead.files.wordpress.com/2008/04/clip-image0094.gif){kind=small}
![clip_image010[4]](http://techahead.files.wordpress.com/2008/04/clip-image0104.gif){kind=small}
![clip_image011[4]](http://techahead.files.wordpress.com/2008/04/clip-image0114.gif){kind=small}
![clip_image012[4]](http://techahead.files.wordpress.com/2008/04/clip-image0124.gif)
![clip_image013[4]](http://techahead.files.wordpress.com/2008/04/clip-image0134.gif){kind=small}
![clip_image014[4]](http://techahead.files.wordpress.com/2008/04/clip-image0144.gif)
![clip_image015[4]](http://techahead.files.wordpress.com/2008/04/clip-image0154.gif){kind=small}
![clip_image016[4]](http://techahead.files.wordpress.com/2008/04/clip-image0164.gif){kind=small}
![clip_image017[4]](http://techahead.files.wordpress.com/2008/04/clip-image0174.gif)
close. So that it will run after the grid completely rendered and hence it will select the radio button.
