T-SQL optimization tips (4): cursor

The cursor mentioned here is of course the transaction-SQL cursor. Any type of cursor will reduce the performance of SQL Server. However, the reason why the cursor exists is that it is still inevitably used in some situations. What we should do is to avoid the cursor as much as possible.

If you need to perform a row-by-row operation on the record set in the T-SQL, consider using the following scheme instead of the cursor:

• Temporary table
• While Loop
• Derived table
• Associate subquery
• Case
• Multi-Query

The performance of the alternatives listed above is also different. In some cases, some schemes may even have adverse effects on the performance, but they are better than the performance of the cursor. The reason for listing alternatives with different performance is that, due to different data structures, it is very likely that it is not easy to use any alternative scheme to replace the cursor, in this case, you can consider other alternatives.

If you must use a cursor in the T-SQL, you should at least optimize the cursor itself:

1) Reduce the number of rows in the record set to be processed: if the object to be processed by the cursor is not all the records in the original table, you can consider placing the data subset in a temporary table first, so that the cursor can only process data in the temporary table. The number of columns in the record set should also be minimized to obtain only the data required by the client. The smaller the record set, the less resources it occupies and the higher the performance.

2) If the number of rows in the result set returned from a query is small, and you need to perform row-by-row operations on the result set, do not use a server-side cursor, to send the entire returned result set to the client, the client completes the necessary operations on each row and returns the updated row set to the server. If the data volume is large, you should consider using the server-side key set cursor instead of the client-side cursor. The performance will be improved due to the decrease in network communication between the server and the client. Of course, you should try these two different cursors under the actual load to determine which cursors have better performance.

3) if a server-side cursor is required, try to use a forward-only cursor (forward_only) or a more optimized fast forward-only cursor (fast_forward. Further, if you cannot use these options, you can use dynamic, static, and set cursors to sort them in a descending order;

4) Avoid using static cursors and key set cursors whenever possible. They need to create temporary tables in tempdb to increase server resource overhead;

5) when using the tempdb database to store cursor data, the tempdb database should be located on its own physical device to speed up the reading of cursor data, thus improving the performance of the cursor.

6) The use of the cursor may lead to lower concurrency performance and may lead to unnecessary locking or blocking. If possible, use a read-only cursor. If you want to update the cursor, you should specify the optimistic option for the cursor to avoid locking the row. Avoid using a cursor with the scroll_locks option, otherwise, the concurrency performance will decrease.

7) after you use a cursor for necessary operations, it is not enough to close the cursor. After the cursor is closed, you should also delete the reference to the cursor (deallocate ). Delete a cursor reference to release the SQL Server resources occupied by the cursor. If you close the cursor only, the lock will be released, but the SQL Server resources occupied by the cursor will not be released.

8) If possible, move the cursor to the last row of the result set as soon as possible to quickly load the cursor. This allows you to release the share lock when the cursor is created and remove the occupation of SQL Server resources.

9) If you need to perform the join operation in the cursor, the key set cursor and static cursor are generally faster than the dynamic cursor. Therefore, you should try to use the key set cursor and static cursor in this case.

10) if the transaction contains a cursor (this should be avoided as much as possible), ensure that the number of rows to be modified by the cursor cannot be too large, and the modified row will be locked until the transaction is completed or canceled: the more lines you want to modify, the more lines you lock, the higher the probability of resource competition on the server, and the lower the performance.

11) Local cursors have better performance than global cursors. However, if you need to use the same cursor multiple times in a batch processing or use the same cursor in multiple stored procedures, you should consider using a global cursor, in this case, the cursor and the data contained in the cursor are prepared in advance, which can save some processing time.

12) if the expected result set to be processed is large, use Asynchronous padding cursor: if the SQL Server Query Optimizer estimates that the number of rows returned by the keyset driver or static cursor exceeds the value of the sp_configure cursor Threshold Parameter, the server starts another thread to fill the worksheet. Control Right immediately returns to ApplicationProgramThe application starts to extract the first row in the cursor, instead of waiting until the entire worksheet is filled. In fact, this will not really improve the speed, but it can give end users a better user experience.

13) if an error is found during the use of the cursor or the processing is completed in advance, the loop should jump out as early as possible before traversing the entire row set.

DOS & donts--
5. Avoid using cursors;
[Note: to attract attention, all DoS and donts summarized in this series areWith certain prerequisitesThe general premise is "unless necessary" and "unless necessary".]