MS SQL Server - When is a CURSOR good?

Question

Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure.

Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same.

My question is when do you use a CURSOR and in what situations are they useful or good?

If there is no use, why would they make such a bad control structure/type for SQL?

Solution 1

Normally they are to be avoided, but the feature is there for a reason and there are times to use them. I'd say 90+% of the cursors I've seen are not needed. If you are using them for CRUD operations, that can almost always be redone in a set-based fashion. I've often seen people use cursors for this because they don't know how to use joins in an update or delete or that they can use a select statment instead of a values clause in an insert. Another unnecessary use when people think they need them for slightly more complex processing that actually could easily be handled with a case statement.

Cursors are sometimes faster for calculating something like a running total.

Cursors are also handy for multiple executions of a stored proc that is set up to handle only one input value at a time. I do not use this feature for running user stored procs (unless I know I will be hitting a very small set of data) but it is very handy for database admins when needing to run system procs against multiple tables.

If you are creating emails in SQl (not the best place to do it, but in some systems that's where they do it) and do not want the entire audience of the email to see the other people on the list or you want to personalize each email with information about the addressee, cursors are the way to go.

Cursors or loops can be used also to process batches of records if the entire set-based insert/update/delete will take too long and lock up the tables. This is a sort of a hybrid between the cursors and the set-based solution and is often the best one for large changes on production systems.

Solution 2

I asked a guy on the SQL Server team one time, if you could add one feature that would make the product better for everyone what would it be?

His response was 'Add? Huh, I would take one away. If you get rid of cursors you force programmers all over the world to start thinking about things in a SET based way and that will be the biggest world wide increase in DB performance you will ever see.'

For my part however I tend to see a pattern, there seems to be a lot of procedural coders who use cursors because they need to be able to do an operation one element at a time and miss the old fashion WHILE loop concept. Same basic idea without the cursor overhead. Still not near as fast/effective as something SET based but 90% of the time when someone claims 'I cant do this set based, I have to use cursors' I can get them to do it with a while loop.

Solution 3

The MCTS prep manual for SQL Server 2008 that I'm studying recommends using external CLR code anywhere that a CURSOR would be required in T-SQL, especially now that SQL Server 2008 supports custom aggregate functions.

5 years ago, I worked with them for extensive reporting features, but I don't think I could come up with a good use case for them now. CLR aggregates and functions perform similarly to built-in aggregate functions.

Solution 4

Here's an article by a rather opinionated fellow, who gives reasoning for not using Cursors and some answers as to how they came to be: There Must be 15 Ways to Lose Your Cursors.

Solution 5

Only time I'll use them is when whatever is been done inside the cursor absolutely has to be done one item at a time and where whatever is been done inside the cursor takes so long that the overhead of the cursor fades into insignificance.

Eg database backups, integrity checks, index rebuilds. In short, admin tasks.

View more solutions

Comments

[David Andres]

+1 for giving solid reasons why Cursors exist. Too many people think set-based logic somehow (a) covers all possible scenarios (b) is always more performant than a cursor-based solution, and (c) is always more maintainable/readable. While I think that the lion's share of work is best done by set-based approaches, nothing is ever 100%.

[Vinko Vrsalovic]

They are heavily misused, which is what that guy probably referred to, but the question was about where to use them. They do have a place in certain cases.

[David Andres]

That hasn't been my experience at all. Invoking CLR methods seems to have its own overhead, and it you're calling such a method in a loop that is run with a significant enough volume, things tend to slow to a crawl. How have things been on your end?

[nuiun]

Pretty good so far, although admittedly I've been using simple functions. Are you talking about aggregates specifically?

[RMorrisey]

FWIW, I think you could probably implement a running total using a Common Table Expression if you're using SQL Server 2005/2008.

[OMG Ponies]

Does SQL Server have implicit cursor syntax? Explicit is the usual declaration with OPEN, FETCH, CLOSE approach...

[B Bulfin]

Am I the only guy who is thoroughly familiar with joins but has no idea how to use a cursor?

[Martin Smith]

CLR aggregates would be more useful if IsInvariantToOrder was implemented rather than reserved for future use. Some performance figures backing up CLR vs Cursors are here

[d-_-b]

@recursive, Having avoided cursors from the get go, I'm in the same boat. Now that I know how to use them, I wondered what they are for.

[batbrat]

@HLGEM, I don't quite understand why cursors are better for the email scenario. I'm re-introducing myself to cursors after a long time right now, so I'd really appreciate a simple explanation.

[dburges]

@batrat, suppose I wanted to send a bunch of sales reps an email with the tasks they are individually respobsible for doing before some dealine like the end of teh quarter. Each email wiil be substantially teh same but some details will be differnt. You don't want JOhn to see what Mary has been tasked to do. So you use a cursor to crete individuals emails that only go to the addressee and have details that differ on the section that lists the tasks.

[micahhoover]

By "same basic idea without the cursor overhead" are you suggesting while loops don't have the same overhead as cursors?

[WayTooSerious]

Using a WHILE loop to do an operation one element at a time instead of using a cursor to do an operation one element at a time does not change the operation into a set-based operation.

[Jan]

I would like have a word with that guy too. I think about Set-based aproaches; spent hours on trying to write them; search the net which tells me: "no set based way, use a cursor"; then I visit sites like this to learn when to use Cursors and only read stuff like: "use Set-based unless you cant". So maybe they should start designing a language that is more read-/writeable WITHOUT(100%) the need of loops instead of blaming procedural coders for using features that where implemented to solve 10% even they couldn't do set-based. Implementing a faster interpreted loop would also do the trick. :(

[Jan]

You are saying (nr1): Try to reach your destination faster than a plane, if you fail grab a plane. This could take my whole life...

[Dane]

@Jan That's not even close to what I was saying (9 years ago). In fact, I don't even know what you mean. Read it all over again please.

[Jan]

So how can you know that it is not even close to what you were saying, if you did not understand what I meant? Sorry for the coment at all, I am just mad at SQL and keep on reading: "use a set based aproach unless you cant". Thats some sort of recursive acronym like GNU. I would need to test my whole life to do it set based, then when I died its safe to say "For ME it was not possible, I could have used a cursor here".

[Dane]

@Jan sorry for not getting back to this comment after you posted to clarify. Your initial question is very fair, and my comment was posted hastily. Anyhow. (1) Some operations in SQL just can't be done in a set operation. (2) Some could be done as a set operation, but due to the size of the data set, shouldn't be done that way, but rather in a chunked set operation. (3) Some operations can't be done in a set approach no matter what, and even for those, only some conditions call for a cursor, if you don't want to involve the application layer. It's our job to know the difference quickly.