Are cursors ever really the "right" choice?

Question

Sometimes procedural programming is absolutely unavoidable when processing data.

I am currently working on optimizing some legacy code. It uses a cursor, 63 pairs of IF/ELSE statements and BEGIN/END's etc. I had hoped to reverse engineer the cursor and make it a procedural process. Now I'm at the end of decoding the algorithm and I realize . . . ooops...it has to be procedural because each choice made on a record depends on the outcome of the process on all the preceding records.

So now I'm torn...there are other choices for mixing procedural code in with SQL Server processing (CLR SPs, UDF's etc.). I'm a big believer in using the right tool for the job, so I'm leaning towards making a .NET CLR SP for this. But it would be faster and "easier" to just simplify the cursor a bit, but still keep the cursor.

What do you all think? Now that we have .NET modules accessible from withing SQL Server, is it ever appropriate anymore to use cursors (which in my opion were a kludge/work around to begin with).

Charles Bretana · Accepted Answer · 2011-11-13T16:21:24.323

2

With SQL server at least, whih has both session nd global temporary tables and table variables, I can not envision a scenario where I would choose to use a server-side cursor. Not all code can be set-based, as you've discovered with yuor legacy app (Are you sure there's no alternative?) but even of you have to iterate through records procedurally, a cursor is the worst choice available.

using a table variable, for e.g., (And this approach starts to degrade in performance for very large table sets)

 Declare @Pks Table (pk integer primary key not null)
 Insert @pks(pk)
 Select pkcolName from table where ... [here put logic to 
           extract id values for rows you need to iterate over

 -- then put procedural code here ...
 Declare @pk Integer
 While Exists (Select * From @pks) Begin
     Select @pk = Max(pk) From @pks -- assuming you need to work 
                             -- on pk values from highest to lowest
     // Here do work on one record at a time, using value in @pk
     Delete @pks Where pk = @pk
 End

edited Nov 13 '11 at 16:21

answered Nov 13 '11 at 00:38

Charles Bretana

143,358
22
150
216

What would you recommend for iterating procedurally other than a cursor? And no, this has to be procedural. I have thought about this a lot and torn it apart and there is no way to do it set-based. – richard Nov 13 '11 at 00:46
[I doubt that this will be any more efficient than using a cursor with the correct options specified](http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html) – Martin Smith Nov 13 '11 at 09:10
Im not completely sure, but I think the only type of cursor that would compare favorably would be a forward looking read only cursor, which is, arguably, not a server side cursor at all but just a syntactical representation of what the code above does... without the control that doing it directly gives you. – Charles Bretana Nov 13 '11 at 15:41

score 0 · Answer 2 · answered Nov 13 '11 at 03:45

I would just run a loop in C# on the client/app server whatever, invoking stored procedures as needed. Typically C# is much faster and easier to develop and unit test, and it can run faster than a stored proc that does everything in the database even if you use CLR.

Are cursors ever really the "right" choice?

2 Answers2