I've recently been asked to look at a system that's been slowly dying and they wanted to know how to fix things. The first sproc I looked at contained a cursor, in fact the system was riddled with cursors.

Cursors are obviously liked by 3GL programmers because they're similar to what they're used to - problem is they usually kill database servers. Is there any reason for still having them on modern databases or should they just be banned?

Mike

I believe (and you will need to wait for another with wider experience with other RDBMSs) that it depends on the RDBMS. Oracle is optimised for use with cursors. SQL Server is not. In fact it is not simply the cursor that T-SQL struggles with - any iterative processing will be slow.

SQL Server specific - Yes - there are reasons for using them. They are over used, especially by procedural programmers. It is more about whether or not they are appropriate. The rule of thumb is if there is a set based answer then set based is the way to go. The exception (in SS2k-) are some flavours of query such as cumulative totals. I use iterative stuff a fair bit for admin tasks e.g. loop through the database catalog and execute a backup\ reindex etc. command.

pootle flump ur codings are working excelent.

That hits the nail on the head imo.

I used Oracle for just a few years (didn't like it) but even it worked faster with traditional methods.

Admin stuff I could accept but having them in applications/reports etc must be a no-no. The issue seems to be that dev people test their code with 10-100 rows and the cursor seems fine - they then go live with 1m rows and wonder why things are slower.

I really can't see how that could be. How would you write a report without using a cursor? (And if the answer is to use a 3rd party tool like, say, Crystal Reports: how could it produce formatted, paginated report output without using cursors?)

I do agree they shouldn't be used for everything - you seem to be saying they shouldn't be used for anything.

Oracle is the only database engine that I know of that still uses something called a cursor for returning results to a client. A cursor to the rest of the database world is a widget that returns a single row at a time to the calling code (whether local to the server like PL/SQL or remote like COBOL or Crystal Reports).

-PatP

I'm curious Mike - I'm sure you knew this before you posted the thread. Why the question?

pootle flump ur codings are working excelent.

I've never had to use a cursor but then I don't tend to produce paper reports. Normally I just pull the data via a select and the calling program would format the data for the web, excel, email etc. I tend to use web reporting so I use drill downs rather than sub totals. I often do a bunch of formating in the SQL itself but I understand people frowning at that. I can't think of an instance where cursors would have to be used though.

The cursors I'm refering to are those where a sproc (not a 3GL prog) might loop through all the results from a select processing with each row one at a time. These kill performance and tend to create locks etc. I think all the major vendors make use of these cursors ( MySQL, SQL Server, Sybase ).

I just came accross a bunch of crappy code full of cursors and wondered why database vendors still use them. Perhaps one of you guys has a good reason for requiring them. I'm also a little bored. It's not a barbed question though if you're worried

Mike

Spend a bit of time on the SQL Server forums and you won't be surprised that even commercial products are littered with cursors. It is nothing to do with requirements and everything to do with ignorance. Of course we haven't seen the code you are referring to and don't know the RDBMS but a fair bet is it is unnecessary.

Until satisfied otherwise I will always assume an agenda. I would not go so far as to say worried though. I think you just like to play around and have over stepped the line on occasion.

pootle flump ur codings are working excelent.

I remember the first time I tried using a cursor...
Man how I was flamed (with good reason, as I learn(t/ed?))!

I believe it was you, Poots, who helped me (re)discover the wonder that is set-based programming!

It was for your own good. The flaming hurt me just as much as it hurt you

pootle flump ur codings are working excelent.

That was my original question - is there ever a good reason to use cursors? and, if not, why are they still with us?

Mike

They are still with us because sometimes you need them. Even in MSSQL.

can we have an example?

You mean you can't think of any examples?

<\Sigh>

a) Dynamic SQL execution.
ii) Executing code on a series of database objects.
3) Non-linear algorithms.