Which is faster in SQL, While loop, Recursive Stored proc, or Cursor?

Question

Which is faster in SQL, While loop, Recursive Stored proc, or Cursor? I want to optimize the performance in a couple of spots in a stored procedure. The code I'm optimizing formats some strings for output to a file.

Answer 1

I'll assume you are using SQL Server.

First of all, as someone said in the statements, recursive stored procs, while possible, are not a good idea in SQL Server because of the stack size. So, any deeply recursive logic will break. However, if you have 2-3 levels of nesting at best, you might try using recursion or using CTE, which is also a bit recursive (SQL Server 2005 and up). Once you manage to wrap your head around CTE, it's an immensely useful technique. I haven't measured, but I've never had performance issues in the few places where I used CTE.

Cursors on the other hand are big performance hogs, so I (and half the internet) would recommend not to use them in code that is called often. But as cursors are more a classical programming structure, akin to a foreach in C#, some people find it easier to look at, understand and maintain SQL code that uses cursors for data manipulation, over some convoluted multiple-inner-select SQL monstrosity, so it's not the worst idea to use them in code that will be called once in a while.

Speaking of while, it also transfers the programming mindset from a set-based one, to a procedure-based one, so while it's relatively fast and does not consume lots of resources, can still dramatically increase the number of data manipulation statements you issue to the database itself.

To summarize, if I had to make a complex stored proc where the performance is paramount I'd try:

1. Using set-based approach (inner selects, joins, unions and such)
2. Using CTE (clear and manageable for an experienced user, bit shady for a beginner)
3. Using control-flow statements (if, while...)
4. Using cursors (procedural code, easy to follow)

in that order.

If the code is used much less often, I'll probably move 3 and 4 before 1 and 2, but, again, only for complex scenarios that use lots of tables, and lots of relations. Of course, YMMV, so I'd test whatever procedure I make in a real-world scenario, to actually measure the performance, because, we can talk until we are blue in the face about this is fast and that is slow, but until you get real measurements, there is no way to tell whether changes are making things better or worse.

And, do not forget, the code is only as fast as your data. There is no substitution for good indexing.

Answer 2

D) None of the above.

A set-based method will almost always be the fastest method. Without knowing what your actual code is (or a close approximation) it's hard to say whether or not that's possible or which method would be fastest.

Your best bet is to test all of the possible methods that you have and see which one is truly fastest.

Answer 3

If you want to improve performance then you need to look at SET based operations, While loops and cursors are basically the same thing. SQL works in SETs, it is not a procedural language, use it how it is intended to be used

Answer 4

Recursive stored procedure is likely to be slowest, while loop and cursors are not mutually exclusive. Cursor operations are pretty quick (IME), but I've only ever used them from external (non-SQL) code. The other posters are correct, if you can do your processing in a set-oriented manner you'll get the best performance.