Updating through cursors

Question

Updating through cursors

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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