Results 1 to 10 of 10

Thread: cursors are bad

  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: cursors are bad

    These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..

    Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.

    Using a cursors to pull records and update them.

    vs

    Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.

    Be nice. I have to work with these guys.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In my experience, T-SQL cursors are a generally bad idea. Most books I have read use the first page of the cursors chapter to say how bad cursors can be, then go on to describe how to build one.

    In order to see how cursors impact performance, I have found that the best way to show people how bad they are, is to show them a perfmon with a pretty line showing CPU usage. Then run the cursor, and point out the big spike. Then ask them to imagine what happens when 4 or 5 more people run that cursor at the same time....

    You should also mention that multiple application servers can be added on, but you will always be cursed with a single database machine. If the database machine is resource bound, no amount of extra application servers can help you.

    As for how ADO stacks up, you may have to stage a race between a couple of processes. One T-SQL cursor based, and the other ADO based, and see who wins.

    Hope this helps.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And nested cursors are extremely bad...

    The must open the first,
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close
    then open then next, Fetch and then close


    Put a dress on a cursor and you know what you have?

    Anyway...You could just do it a set based method..

    Got a sample of some code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least in my experience, set-based solutions are at least one order of magnitude (ten times) faster than cursors in Microsoft SQL. They are often much faster than that, but the 10 times benchmark is easy to hit so that's all that I claim up front.

    In every form of data processing, the closer you can keep the processing to the hardware itself, the faster the process runs. Cache is faster than disk, set based solutions on the SQL Server are faster than cursors, which can be faster than ADO under the right circumstances.

    Rather than argue for ages or produce/quote meaningless benchmarks, I'd suggest you try it. Take a very simple process that affects many rows and rewrite it as either ADO, set based, or both. Compare doing the same task all three ways.

    I'm confident that the set based solution will comfortably outperform any other way of solving the problem. ADO will probably come next, depending on the amount of network I/O it has to do. I'd expect the cursor to come in dead last.

    I'd really like to hear your results when you get them!

    -PatP

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    thanks guys.

    I have what looks like a few hundred .sql files. I have done set based querying before. I am not as tough as you guys but I am studying everyday.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can post or attach a small one so we can take a look?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if it was my code I would but I probably should'nt because I gave the CIO a link to the thread.

    95% chance they won't do anything with the advice. there is never any time or resources for anything here. they brought me into fix things and they tied my hands in the first week I was here by telling me I could not change any of the existing stuff and everything had to be a workaround.

    I have told myself I have to stick it out for a year and then I would evaluate the situation. It's almost been 6 months.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...

    What I would do.

    From a client, use Profiler to record some statistics.

    Then "clone" and existing cursor process with a set based solution. Give us some "examples" that are close to what yo uhave so we can help.

    Make sure the results produced are identical...write them to work tables for proof.

    Publish your results.

    If the results are anywhere what I expect it should be clear that it should be rewritten...it can be done a little at a time, and not everything has to be done...just find the biggest offenders...

    How big is the database?

    I'd dump and restore a copy of production so you can have your own dev environement...

    Anyway...good luck

    CIO

    Constantly Ignorant Officer
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Check you calendar. It's probably only been two weeks, and just SEEMS like six months.

    How are you supposed to fix anything if you can't change anything?

    Do you take your car into the shop and say, "Make it run better, but don't touch the engine?"

    Seems like the only thing you could recommend would be a massive upgrade to the hardware. See how they like the price-tag on that.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Blindman,

    Trust me I know. They have already talked hardware. In my last company, a few years ago when I was just a pup of a developer they beefed up the db server hardware for crappy application they were running and saw little or no gain because the code was so bad. they finally revamped the app and it was'nt until then that things got better.

    I am thinking about switching auto shops. I am going to reproduce things on my local like Brett said when I get a chance. Maybe this weekend. I have app development to do today.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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