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 and take more I/O resources. If you need to perform row-by-row operations, try to find another method to perform the task.
Some the recommended alternative of the cursor are mentioned bellow:
- Use WHILE LOOPS
- Use temp tables
- Use derived tables
- Use correlated sub-queries
- Use the CASE statement
- Perform multiple queries
If there is no alternative, and you need to use a cursor remember some tips related to it. It can save you little more.
1.. One way to do this is to move the records that need to be processed into a temp table first, 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.
2.. 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, then return any updated rows to the server.
3.. 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.
3.. 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, which increases overhead and can cause resource contention issues.
4.. 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 may use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be.
5.. 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.
6.. 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.
7.. 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.
8.. 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.
9.. 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.
10.. 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.
11.. 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.
12.. 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. Contributed by Nataraj Prakash.
Posted By: Mr. Joydeep Das
Not fully agree with you.
ReplyDelete1.. One way to do this .... this equivalent to a static cursor but more everhead than a static cursor
2.. If the number of rows you need... it depends on the network overhead. If network is fast, true.
3.. If you have no choice but to use ... Fast_forward cursor is a dynamic cursor, which means slow although there are some optimization done internally.
3.. Avoid using static/insensitive and keyset cursors.....static cursor is the fatest cursor interms of looping. Comparing all type of cursor from creation to the end of looping, this type of cursor in most of the time is the fastest one. although there are some tempdb overhead, but better than your method one.
4.. If you have no choice but ... tempdb optimization should be something must done while planning deployment of SQL Server. you are right, Tempdb should always be optimized from hardware perspective
5.. Using cursors can reduce concurrency... agreed... the locking behavor in cursor is varied from one type to another.
6.. When you are done using .... agreed but cursors are released when session completes (pooled connection reset session) or end of scope depending on cursor settings. So use local cursor is a better habbit to prevent resource leak and it can fit in most of the cases.
7.. If it is appropriate .... not agree. Shared locks are released when data gets read to the cursor. regular dynamic cursor use IS lock on the page, S lock on the row and release right after the fetch. Static cursor does not have locking overhead comparing the dynamic cursor while looping.
8.. If you have to use a cursor because your .... log of debating on this. Depends on the decision of interfaces selection -- chatty interface or chunky interface
10.. If you need to perform a JOIN .... not agreed. keyset cursor caches the keys in the tempdb and perform key look up to base table(s). If no key in the result set can be determined, a static cursor will be created automatically. well, Is keyset cursor is a key to a cursor with joining operator? don't think they are corelative.
11.. If a transaction .... agreed, keep transaction quicker to increase concurrency
12.. In SQL Server, .... agreed, but at the last, can local cursor really reduce the resource while it's running? no. It can release automatically when the program runs out of the scope.