3

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 calculation 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.

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
Vishal
  • 12,133
  • 17
  • 82
  • 128

4 Answers4

2

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.

SteveCav
  • 6,649
  • 1
  • 50
  • 52
1

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

Community
  • 1
  • 1
1

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

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I cannot get to this link. As a possible alternative, I have found that the following link gives useful information & examples https://www.sqlbook.com/advanced/sql-cursors-how-to-avoid-them – Kevin Swann Oct 21 '19 at 10:47
-1

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.

SRM
  • 1,377
  • 1
  • 10
  • 17
  • 1
    Many places require all database accdess to be through stored propcedures. And it is a better practice to do complex processing in a stored proc that the dba can performance tune than to put things like this on the application. Further almost all cursors that are run from the application can easily be replaced with set-based logic. Cursors are rarely needed except for some dba-oriented administrative tasks. If you are inserting, updating or deleting from one or more tables, you can do that in a set-based way pretty close to 100% of the time. – HLGEM Nov 22 '10 at 23:20
  • I've never understood why people are so insistent on keeping business logic out of the database. I've yet to encounter a system where the data could truly be manipulated in a meaningful manner without that logic. Never mind the fact that we change programming languages far more often than we do database vendors. – NotMe Nov 22 '10 at 23:36
  • 2
    It's probably a matter of perspective and a choice of language. Programmers native language is going to be whatever HL language they happen to be programming in at the time. DBA's native language is SQL. It makes sense that one would fall back on the familiar. – SRM Nov 22 '10 at 23:42
  • I agree that perspective / language choice has a lot to do with our design decisions. – NotMe Nov 22 '10 at 23:58