Post reply

Updating through cursors

  • July 24, 2008 at 5:30 pm

    #848146

    Thanks for the feedback, Hugo,

    I explained why a merry-go-round scan is not possible for this in the article. I even demonstrated what a merry-go-round scan is... updates don't do merry-go-round scans on Clustered indexes especially when the index "hint" forces the index to be use. I put "hint" in parenthesis because when it comes to index hints, it's not a hint... it's a mandatory directive. MS even warns against such a practice for "normal" queries because it overrides the optimizer and most people aren't smarter than the optimizer.

    I also appreciate your extreme testing and reporting back on you found. That's awesome. Thanks.

    If anyone can cause the code to fail on an unpartitioned table, I'd sure be interested in seeing it... I'm not beyond publishing a retraction of my findings if I'm proven wrong or publishing an update to the article for an additional "exception."

    Thanks folks.

    --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)
    Intro to Tally Tables and Functions

  • July 10, 2009 at 1:32 pm

    #1023300

    Great stuff/thread. Wish i could have made it to that Pass session.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 2, 2010 at 5:43 pm

    #1230741

    The chosen answer is wrong, the right answer is "it depends". The whole point of the "For Update" mark on a cursor declaration is to ensire that ITU locks are taken early, not late; in cases where there is risk of deadlock, this minimises the both the frequency of deadlock and the amount of work that will be thrown away (rolled back) when deadlock occurs.

    It's not surprising that it's easy to demonstrate that a cursor definition designed to optimise performace in cases where there is significant c=oncurrent access and risk of deadlock will not be optimal when it's the only query being run against the data concerned. It's also not useful to make that demonstration.

    Tom

Viewing 3 posts - 16 through 17 (of 17 total)

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