Saturday, April 04, 2009
The general advice within the SQL Server community is do not use any T-SQL cursors at any time.
I must say that I have to agree, although if you have browsed my blog you've noticed that there are a couple of occasions when I do use cursors but only when the following is true:
- All cursors are read_only / forward-only for optimum speed.
- They're not used in OLTP environments.
- Datasources are small sets, usually system objects, like a list of databases / tables.
- The impact the cursor is many times smaller then the processing steps taken with the row results.
(like a cursor based on sys.databases and with the result of each row a full database backup is made).
- Avoid using cursors and try solve the problem by using set based operations.
- Try to base them on readonly tables or local (#temptable) temporarily objects.
- Cleanup! Always CLOSE and DEALLOCATE cursors.
- Specify the 'LOCAL' option.
- Specify the FAST_FORWARD clause on DECLARE CURSOR. This opens an optimized forward-only, read-only cursor.
- Never use cursors in OLTP environments.
- Never use them as source of an update: UPDATE table SET col1 = 2 WHERE CURRENT OF the_cursor;
- Cursors are memory intensive; base them on smaller sets (less 5,000-10,000 rows, your site could differ: test!).
If you do need cursors in high load OLTP environments because of some complex calculation that can't be done set based take the following approach:
Copy the required result set in a temporary object. Retrieve only the rows and columns you need, but do include all the fields of the primary key.
create #temptable (CalcValue int, pkCol int)
INSERT INTO #temptable (CalcValue, pkCol)
SELECT 0, PrimaryKeyCol
WHERE -- your where clause here
Base your cursor on the temp. object.
Loop the cursor, perform your calculation and store the result in the temp. object row:
UPDATE #temptable SET CalcValue=complex_calculated_value
When done looping close/deallocate the cursor, and update the high load OLTP source table(s) set based by primarykey, use a BEGIN TRANSACTION / COMMIT if required:
UPDATE dbo.HighLoadOLTPtable SET CalculatedValue = #temptable.CalcValueFROM dbo.HighLoadOLTPtable, #temptableWHERE dbo.HighLoadOLTPtable.PrimaryKeyCol = #temptable.pkCol
see my posts on TempDB configuration and TempDB objects