How slow are cursors really and what would be better alternatives?

I have been reading that cursors are pretty slow and one should unless out of options avoid them. I am trying to optimize my stored procedures and one of them uses a cursor. It frequently is being called by my application and with lot of users(20000) and rows to update. I was thinking maybe I should use something else as an alternative.

All I am trying to do or want is to get a list of records and then operate on depending on each row value. So for e.g we have say -

Employee - Id,Name,BenefitId,StartDate,EndDate

So based on benefitId I need to do different calc开发者_如何学Pythonulation using dates between StartDate and EndDate and update employee details. I am just making this contrived example to give a idea on my situation.

What are your thoughts on it ? Are there better alternatives for cursors like say using temp tables or user defined functions? When should you really opt for them or should we never be using cursors ? Thanks everyone for their help.

---

I once changed a stored procedure from cursors to set based logic. Running time went from 8 hours to 22 seconds. That's the kind of difference we're talking about.

Instead of taking different action a record at a time, use several passes on the data. Update and set field1=A where field2 is X, then update and set field1= B where field2 is Y, etc.

---

I've changed out cursors and moved from over 24 hours of processing time to less than a minute.

TO help you see how to fix your proc with set-based logic, read this: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

---

A cursor does row-by-row processing, or "Row By Agonizing Row" if your name is Jeff Moden.

This is just one example of how to do set-based SQL programming as opposed to RBAR, but it depends ultimately on what your cursor is doing.

Also, have a look at this on StackOverflow:

RBAR vs. Set based programming for SQL

---

First off, it sounds like you are trying to mix some business logic in your stored procs. That's generally something you want to avoid. A better solution would be to have a middle tier layer which encapsulates that business logic. That way your data layer remains purely data.

To answer your original question, it really depends on what you are using the cursors for. In some cases you can use a table variable or a temp table. You have to remember to free up temp tables though so I would suggest using table variables whenever possible. Sometimes, though, there is just no way around using cursors. Maybe the original DBA's didn't normalize enough (or normalized too much) and you are forced to use a cursor to traverse through multiple tables without any foreign key relationships.