Cursors vs duplicate code/logic

Question

Hello,
I heard it's not good to use cursors, as they "unnatural" to DBMS and they provide bad perfomance.
But imagine the following situation: I have a stored procedure and I need to call this stored procedure for every customer from France (for example). I have a few options, such as using cursor, write all stuff in one query and call stored procedure for every customer from the client-side-application.
If I write all stuff in one query - it will most likely duplicate code/logic/whole query from already existing stored procedure. And it looks like a "smelly approach" (if you read "Refactoring" book) to me. The logic is not incapsulated in one place anymore.

What do you think?

PS. Links to any documents describing why cursors are bad or not bad are welcome.

Answer 1

Depending on how your store procedure was written, you might be able to convert it into a function if you are using SQL Server. With functions you can do something alone the lines of:

SELECT uf_MyFunction(customer_id, customer_name, customer_address) FROM Customer

to apply it to every single customer record

Answer 2

If you're committed to having business logic in the form of stored procedures on the database then a cursor is not bad.

Assuming you have a pretty standard client-server-database architecture it is probably a better idea to move the logic out of the database and into the app server. This has a couple benefits:

1. Better scalability. Its easier/cheaper to add app-servers than database servers.
2. Centralizing business logic. An application with business logic spread all over is harder to maintain.

Answer 3

Cursors are not necessarily bad, it is just that in most cases where your instincts tell you to use them, there is a more performant way to do the same thing using a declarative or set based approach. If you post the specifics of your proc, I bet you will get some good suggestions for how to do what you need with a single stored proc call and no cursor.

Answer 4

Also bear in mind that cursor performance will vary from RDBMS to RDBMS.

However, I think that a case can be made that cursors are unnatural to a SQL database (and there are some experts on the subject who would argue this). If you think about it, a cursor can be thought of as an iterator (or even a pointer if you really want to be nasty). And while iterators work well in procedural languages, they don't fit in well with a declarative language like SQL.

Now, I haven't really used cursors enough to agree or disagree with that line of thought. But I will say that when I think about it, I can't really think of any queries that I've written that were simplified by using a cursor (not to say they don't exist).

Answer 5

There are times when a cursor is the right tool to use. There are other times when it's better to retrieve an entire query, and operate on it as a set.

SQL has a lot of set oriented operations built into it. For example, an UPDATE can operate on an entire set of rows from a table. If there's a WHERE clause, those are the rows that will get updated. The update can use context sensitive subqueries and CASE constructs to provide a lot of flexibility in terms of updating different rows in seemingly different ways.

Expressing a monumental data transformation as a single UPDATE can seem like a daunting task to a programmer who is just coming up to speed on SQL. It's so much easier to declare a cursor, loop through the rows returned, treat each row as a record, and revert to one record at a time processing. As long as your involvement with databases remains "lite", that may be good enough for you.

But if you expect to build industrial strength databases, it behooves you to learn how to manipulate data in terms of sets of rows, and not just one row at a time. You'll get better performance. Perhaps more importantly, you'll get better clarity about the relastionship between the underlying business rules and the code you've written.

It's much easier to operate on sets of data in a well designed database than in a poorly designed database. If you're just coming up to speed on database design, and just coming up to speed on SQL queries at the same time, you might want to get a mentor to advise you on your database design. If you don't do that, you may have a hard time learning the power and simplicity of set oriented operations.

And, there are still times when you'll use cursors.