Why Cursors so bad

  • Hi Expertz

    Can anyone explain to me why cursor take longer time for execution.

    Why cursors effect performance.

    How to avoid clustered and non-clustered index Scan's and also table scans.

    Which is better if i do not need to update cursor result set:

    DECLARE c1 CURSOR

    or

    DECLARE c1 CURSOR READ_ONLY

    or

    DECLARE c1 CURSOR FAST_FORWARD READ_ONLY

    or

    DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY

    I need expert opinions on this , that's why i posted it here rather than searching in google .

    Please help

    Tanx 😀

  • Start here[/url].

    Then here[/url].

    Any questions after that, ask right back here or in the discussion threads of the articles above.

  • How to avoid clustered and non-clustered index Scan's and also table scans.

    Which is better if i do not need to update cursor result set:

    DECLARE c1 CURSOR

    or

    DECLARE c1 CURSOR READ_ONLY

    or

    DECLARE c1 CURSOR FAST_FORWARD READ_ONLY

    or

    DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY

    Tanx 😀

  • Have you read the articles that I asked you to read yet in my post above? If not, please go read them.

  • Eswin (6/25/2009)


    How to avoid clustered and non-clustered index Scan's and also table scans.

    Indexes that support the queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Eswin (6/25/2009)


    How to avoid clustered and non-clustered index Scan's and also table scans.

    Do what Gail says. Avoid functions on columns in the WHERE clause and JOINs. Use operations that can use indexes, avoid operations that can't... There's more. Just keep reading.

    Which is better if i do not need to update cursor result set:

    DECLARE c1 CURSOR

    or

    DECLARE c1 CURSOR READ_ONLY

    or

    DECLARE c1 CURSOR FAST_FORWARD READ_ONLY

    or

    DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY

    It depends. Do you need to move backwards as well as forward? Then you can't use forward_only or fast_forward. Do you need to update data in the cursor? Then you can't use read_only.

    Cursors operate on each row, one at a time. SQL is a set based language designed to work on blocks of data. Which is faster, one thing at a time or lots of things at once?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (6/25/2009)


    Cursors operate on each row, one at a time. SQL is a set based language designed to work on blocks of data. Which is faster, one thing at a time or lots of things at once?

    An example I heard once.

    You want to put some rice into a saucepan. Which is faster, scooping a cup of rice into the saucepan or moving the rice one grain at a time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Oooh. That's a good one.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (6/25/2009)


    Oooh. That's a good one.

    I'd give credit, but I have no recollection who initially said it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Lynn Pettis (6/25/2009)


    Have you read the articles that I asked you to read yet in my post above? If not, please go read them.

    I know he is slow and a whole lot lazy.

    But still while using it i wanted to know which is the better option.

    Tanx 😀

  • Eswin (6/26/2009)


    Lynn Pettis (6/25/2009)


    Have you read the articles that I asked you to read yet in my post above? If not, please go read them.

    I know he is slow and a whole lot lazy.

    But still while using it i wanted to know which is the better option.

    Best option? Okay, except for certain maitenance routines, you should avoid cursors as much as possible. There may be a use for them, but you should really document why you are using it and it should be the option of last choice.

Viewing 11 posts - 1 through 10 (of 10 total)

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