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.