Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Cursors - is there any reason for having them?

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by Poots
    if there is a set based answer then set based is the way to go
    That hits the nail on the head imo.
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by Pootle
    Oracle is optimised for use with cursors
    I used Oracle for just a few years (didn't like it) but even it worked faster with traditional methods.

    Originally posted by Pootle
    I use iterative stuff a fair bit for admin tasks e.g. loop through the database catalog and execute a backup\ reindex etc. command.
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by mike_bike_kite
    Admin stuff I could accept but having them in applications/reports etc must be a no-no.
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    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'm curious Mike - I'm sure you knew this before you posted the thread. Why the question?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by andrewst
    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'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.

    Originally posted by Pat
    Oracle is the only database engine that I know of that still uses something called a cursor for returning results to a client
    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 ).

    Originally posted by Pootle
    I'm curious Mike - I'm sure you knew this before you posted the thread. Why the question?
    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

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    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.
    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.

    Quote Originally Posted by mike_bike_kite
    It's not a barbed question though if you're worried
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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!
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It was for your own good. The flaming hurt me just as much as it hurt you
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by Pootle
    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.
    That was my original question - is there ever a good reason to use cursors? and, if not, why are they still with us?

    Mike

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    They are still with us because sometimes you need them. Even in MSSQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by blindman
    They are still with us because sometimes you need them
    can we have an example?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •