Are cursors hard on servers?

  • I was reviewing a process with a colleague and mentioned cursors. He said, "Please don't use cursors as it is really hard on the server." I know there are case by case instances but is this generally true? Is it considered bad programming to use cursors?

  • Rog Saber (4/1/2014)


    I was reviewing a process with a colleague and mentioned cursors. He said, "Please don't use cursors as it is really hard on the server." I know there are case by case instances but is this generally true? Is it considered bad programming to use cursors?

    In SQL Server, yes. The problem is also partly due to the fact that most people use the default settings of the cursor, which is not efficient in most cases.

    The largest problem with cursors is that they process the data row by row, while SQL Server is optimized to handle set based queries.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Since the IO processing from a cursor is not efficient, what are some other alternatives? I know it will vary on the situation but, are there some things I can research 'generically' to determine a more efficient way?

  • Most of the time cursors can be avoided but it's not a religion, there are tasks which do justify the use of cursors. Obviously if an operation can be achieved using a set based methods, it fits the environment better. But some things like one off operations, continued operation where there are serious exceptions and so forth might justify it. All boils down to "it depends".

    To answer the question if cursors are hard on the servers, "it depends".

    😎

  • I'm not sure that I agree with Eirikur about saying "it depends". I'm sure that cursors are always hard on servers, but some methods can be worse (e.g. triangular joins) and sometimes a well defined cursor is the way to go.

    Cursors are not evil, they're a tool that should be use properly.

    You might want to take a look to these articles:

    There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction[/url]

    There Must Be 15 Ways To Lose Your Cursors… Part 2[/url]

    And since cursors are simply a while loop:

    The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • OK thank you all for the suggestions.

  • Luis Cazares (4/1/2014)


    I'm not sure that I agree with Eirikur about saying "it depends". I'm sure that cursors are always hard on servers, but some methods can be worse (e.g. triangular joins) and sometimes a well defined cursor is the way to go.

    Cursors are not evil, they're a tool that should be use properly.

    Almost saying the same thing here, "it depends" on the total cost of the operation πŸ˜€

    One can concatenate several thousand operations in a dynamic sql string and call in not-cursed thingy, still not set based but not a cursor. Probably a cursor would be better in that situation. I still agree that it should be avoided "when applicable".

  • there are tasks which do justify the use of cursors

    I'm sure this is true, but I haven't run into one yet. Once I thought I had, but I was wrong. If the DO WHILE command is considered a cursor, as some seem to think, then I use one every few years to populate my warehouse date dimension.

  • RonKyle (4/2/2014)


    there are tasks which do justify the use of cursors

    I'm sure this is true, but I haven't run into one yet. Once I thought I had, but I was wrong. If the DO WHILE command is considered a cursor, as some seem to think, then I use one every few years to populate my warehouse date dimension.

    Actually often times a well formed cursor can outperform a while loop. However since you are using it to populate tables a while loop is often a decent way to go about it. You can sometimes use a tally/number table to avoid this type of looping but that is not always the case.

    There are many administrative tasks where a cursor is really about the only way to do it. Also, if you are sending personalized emails to a number of people a cursor is often the best choice.

    Good grief....I just typed up two paragraphs defending cursors and April Fool's day was yesterday. I am now going to turn in my Anti-RBAR card.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • RonKyle (4/2/2014)


    there are tasks which do justify the use of cursors

    I'm sure this is true, but I haven't run into one yet. Once I thought I had, but I was wrong. If the DO WHILE command is considered a cursor, as some seem to think, then I use one every few years to populate my warehouse date dimension.

    Depending on your requirements, you might not even need a cursor/while loop to populate the date dimension. You might be able to populate it using a tally/numbers table as we do on our company.

    As Sean said, cursors are good for administrative tasks or maybe multi-database queries.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I would take a cursor over a triangular join if I was reporting on a running total in a columns. That's once instance.

    ----------------------------------------------------

  • MMartin1 (5/23/2014)


    I would take a cursor over a triangular join if I was reporting on a running total in a columns. That's once instance.

    That's ok if you can't (or don't want to) use the quirky update or the windowing functions available on 2012 and 2014.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Eirikur Eiriksson (4/1/2014)


    Most of the time cursors can be avoided but it's not a religion, there are tasks which do justify the use of cursors. Obviously if an operation can be achieved using a set based methods, it fits the environment better. But some things like one off operations, continued operation where there are serious exceptions and so forth might justify it. All boils down to "it depends".

    To answer the question if cursors are hard on the servers, "it depends".

    😎

    To add to that, there are a whole lot of people who simply think certain tasks "justify the use of cursors" because they just don't know differently and that frequently results in the inappropriate use of Cursors and other RBAR. A great and prominent example of this is the number of people that STILL post "splitters" with WHILE Loops and rCTEs.

    As for "Continued Operation where there are serious exceptions", I suggest that using cursors in such a fashion should be replaced by prevalidating data whenever possible... and it's normally possible. To coin a phrase, "There should be no expected failures." πŸ˜‰

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • MMartin1 (5/23/2014)


    I would take a cursor over a triangular join if I was reporting on a running total in a columns. That's once instance.

    A fairly rare exception, indeed. Of course, I wouldn't use either even if I needed to work with something less than 2K12.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply