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

That is like asking which is healthier, drinking Drano, jumping off the top of a high-rise, or getting hit by a bus? Stop thinking iteratively and think about working in terms of sets.

Seriously I try hard to avoid cursors or while loops or recursion when working with SQL. If I do run into a problem where I can't do everything with a simple pipeline of queries sometimes I fall back on having a series of updates to a temporary table where at the end I return the contents of the temp table. In this case it's hard to tell but I'd expect to have some functions that would do the formatting of a single cell of data, and then apply the functions all at once, or in stages if you need to make multiple passes, to a temp table, where each pass is an update with a WHERE clause that specifies what rows of the temp table you want to modify.

Basically, SQL is designed to work a certain way and if you go with the flow you will have an easier time of it. If you're considering using a cursor or while loop then it seems likely you already know what subsets you have because those are the rows the cursor is affecting, specify those rows in a WHERE clause and let the DBMS write the cursor for you.

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 loop and cursor is basically the same thing. SQL works in SETs is 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.

Answer 5

You don't know yet, but you want to read this book.

http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972

Answer 6

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. Howver, if you have 2-3 levels of nesting at best, you might try using recustion or using CTE, which is also a bit recursive (SQL Server 2005 and up). Once you wanage to wrap your head around CTE, it's an immensly 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 maintaing SQL code that uses cursors for data manipulation, over some convoluted multiple-inner-select SQL monstrosity, so it's not the worst idea to used them in a 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 dramaticly 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 aproach (inner selects, joins, unions and such)
2. Using CTE (clear and managable for an experienced used, bit shady for a beginer)
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, it's like a sex convention at a catholic monastery*

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

• that analogy was a lot funnier a couple of years ago, now it's a bit sad.

Answer 7

There's a 3-part blog post about cursors which is worth a read if you've got the time. I also avoid them like plague but this blog has made me see them in a different light... well... I pitty them slightly now but still won't use them!

The Truth About Cursors

Answer 8

Take a look at Cursors and How to Avoid Them which will give you ideas on how to replace cursors with SET based operations