October 9, 2009 at 11:54 am
Are you assuming that this thread made sense at one point?
October 9, 2009 at 11:58 am
Steve Jones - Editor (10/9/2009)
Are you assuming that this thread made sense at one point?
Of course it did! How else do you think Skynet came into existance!?!?
Oops... I probably wasn't supposed to say that.
-- Kit
October 9, 2009 at 11:58 am
jcrawf02 (10/9/2009)
Grant, I'm bummed that I missed the Columbus chapter meeting last night, life happened 😉Don't suppose Jeremiah recorded it, or you have an upcoming session somewhere else on LiveMeeting that I could dial into?
It was recorded, but don't hold much hope. I was dealing with a production issue up until I started the presentation, so I wasn't properly prepared. It wasn't my best night.
"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
October 9, 2009 at 5:22 pm
Steve Jones - Editor (10/9/2009)
Just an FYI, since THE THREAD is so heavily visited. We've started our own ASK site at http://ask.sqlservercentral.com. Not my idea, and I'm not sure how we'll sync up points/rating at some point, but feel free to check it out, post/answer questions there.
[sarcasm]
Yay! We're just like everybody else!
[/sarcasm]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 9, 2009 at 11:25 pm
Gianluca Sartori (10/9/2009)
I had a hot discussion with Joe Celko some days ago about the quirky update.He doesn't seem to like it.
I told him I use the quirky update expecting it to break at some point with service packs or new sql versions, but I use it in the meanwhile because it's fast and I would care about the problems when they arise.
He said that he would never step on a bridge built by an engineer that works this way....
Good point, actually.
Then, he should stay off of bridges. 😛
There's a lot of people that don't like the quirky update and they should stick to cursors to do the same work. Cursors and regular updates are rock steady safe.
Or are they? People are yanking about the undocumented nature of the quirky update... what about something super well documented that no one would ever expect to fail? How about just a normal update? Remember SQL Server 2000 sp2? Do you remember the hotfix they had to come out with because if parallelism took place during the update and certain indexes were just right that UPDATE would ignore a WHERE columnname IS NULL? Well I sure do... and because the very well documented backups also decided to fail, it took 40 people about 10 days to get the system back online because a data cleanup script that was supposed to update just 50 rows, ripped off an update of all 768 customers and no one noticed until it was too late.
How about the wonderful and very well documented sp_MakeWebTask? It used to work just fine in SQL Server 2000 sp3A... until they came out with sp4. We absolutely had to have sp4 to be compliant with our 3rd party vendors. sp4 changed the privs on sp_MakeWebTask with no warning until they published sp4. It took a very long time to reconfig all the privs to SA for all our jobs while still protecting the data.
And let's talk about some real engineering, shall we? Let's talk about some nasty ol' green mold that is the bane of every bread box. It became a life saver in the form of Penicillin. For years, many doctors and scientists warned of the impending "doom" that would someday occur when the "bugs" that it killed would finally become resistant. Did that stop people from using it? No... you know why? Because it was just too valuable at the time.
Same goes for the "quirky update"... use it until it they break it (I hope THAT never happens) because it's just too valuable at the time... if they ever break it, you can always go back to a cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2009 at 11:29 pm
jcrawf02 (10/9/2009)
Gianluca Sartori (10/9/2009)
I had a hot discussion with Joe Celko some days ago about the quirky update.He doesn't seem to like it.
I told him I use the quirky update expecting it to break at some point with service packs or new sql versions, but I use it in the meanwhile because it's fast and I would care about the problems when they arise.
He said that he would never step on a bridge built by an engineer that works this way....
Good point, actually.
No it's not. Engineers design failure into products because it allows them to use cheaper materials and methods, or they evaluate the life expectancy and replace before it gets there. Celko's asking for a bridge that will never fail in this case, which would put a lot of innocent Dept of Transportation workers out of a job (I'm crying on the inside).
If it works, it works, regardless of the level of documentation, and not using it when the benefits are so concrete is just stubborn. You are aware of the risks of it going away at some point, but so what? We all die at some point too. That shouldn't keep you from accomplishing something in life.
Heh... all good points. I'll add that the "quirky update" has been working correctly for longer than some bridges have. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2009 at 11:39 pm
WayneS (10/9/2009)
Jeff Moden (10/8/2009)
Heh... I know it's a couple/three months old now but I've not seen it before.Thanks for bringing this to us Jeff, I hadn't seen that reply. (And since he was responding to me...)
And wouldn't he really get upset if he knew the only reason I added the INDEX(0) option was to try to make nay-sayers like him happy?
So, I'll make it official... the INDEX(0) table hint is just what it says in the comment in the article above... it's "warm fuzzy" stuff that doesn't change a thing except slow the code down a bit (a lot, actually).
He did bring up an interesting thing though, and I'd like your opinion.
BTW, as an aside, the hint index = 0 doesn’t instruct the optimizer to do something in clustered index order, rather, just do a table scan (or clustered index scan with no order guaranteed). To instruct the optimizer to perform an activity in clustered index order you would normally use index = 1.
Would / Does using INDEX(1) do anything for the quirky update?
Look closely... he didn't say INDEX(1)... he said INDEX = 1 and the clustered index is always #1. Take a look at BOL...
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
See anything in there about it forcing the optimizer to perform an activity in clustered index order for INDEX(1)? Nope... just the word "scan". And that's the key. The "quirky Update" follows the row-to-row index known as the clustered index in the proper order even if there's rampant index fragmentation and massive page splits and it does it in the form of two clustered index scans... one for the read and one for the write. I have all those proofs in the rewrite of the article that I'm still polishing.
The real key is, when you follow the rules for a "quirky update", neither INDEX(0) nor INDEX = 1 are necessary because it will do the necessary index scan on both ends without being told.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2009 at 11:51 am
Jeff Moden (10/9/2009)
...Look closely... he didn't say INDEX(1)... he said INDEX = 1 and the clustered index is always #1. Take a look at BOL...
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
See anything in there about it forcing the optimizer to perform an activity in clustered index order for INDEX(1)? Nope... just the word "scan". And that's the key. The "quirky Update" follows the row-to-row index known as the clustered index in the proper order even if there's rampant index fragmentation and massive page splits and it does it in the form of two clustered index scans... one for the read and one for the write. I have all those proofs in the rewrite of the article that I'm still polishing.
The real key is, when you follow the rules for a "quirky update", neither INDEX(0) nor INDEX = 1 are necessary because it will do the necessary index scan on both ends without being told.
You know, what's interesting is that several months ago, I came up with some other ways to insure the order of aggregation, one which appears to work on a much broader number of cases than the original "quirky update", though it's syntax may be a bit more obtuse. Unfortunately, I never really had the chance to fully test it, however, I did come up with the outline of a proof that, so long as Microsoft did or supported certain other things, then it *must* work, even if Microsoft would not support it (i.e., that any failure of it would mean that something else that Microsoft *did* support, must also be broken).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2009 at 1:53 pm
Cool... can you post it? I'd love to see it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2009 at 7:07 pm
RBarryYoung (10/6/2009)
Please, don't get me wrong Paul. I'm not saying that there was a thing wrong with your post, there wasn't. I'm just saying that today the stuff that I was saying (and assuming 🙂 ) six months ago, probably makes a lot more sense now.
Wow but I'm a long way behind here.
Hey no worries Barry - I read your comments as they were intended 🙂
All good.
On the subject of the running totals using the UPDATE syntax - I think it just goes to show that even people like Itzik can miss the point sometimes.
I remember reading the article when it came out - funnily enough a 'one time only' super-discounted subscription renewal offer came a few days later from SQL Server Magazine. I haven't taken advantage yet :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 10, 2009 at 10:46 pm
Jeff Moden (10/10/2009)
Cool... can you post it? I'd love to see it.
Heh. I've been playing this one close to the vest until I had something that I could publish. But I did partly give it away by using a simplified version of it here for Garadin's tests (no I don't mean all of the varchar tricks, it's the other thing in there that I didn't mention :-)). I was surprised that no one noticed that my example worked, even without a matching clustered index. Anyway, if you want to try it out, Jeff, then have at it, and if you think that it's workable and good enough to include in your book, then go ahead. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2009 at 11:22 pm
Thanks for the offer. I appreciate it. Of course, I'll definitely throw the bone in the right direction on that.
By the way... thanks... I lost track of that thread and was looking for it for another reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2009 at 11:56 pm
Jeff Moden (10/10/2009)
Thanks for the offer. I appreciate it. Of course, I'll definitely throw the bone in the right direction on that.By the way... thanks... I lost track of that thread and was looking for it for another reason.
Here's something to try, take my query from there, and look at the Query Plan. Then take off the bit that I am alluding to and look at the query plan again. That's what really got me interested in it for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 12, 2009 at 7:08 am
Gianluca Sartori (10/9/2009)
I had a hot discussion with Joe Celko some days ago about the quirky update.He doesn't seem to like it.
I told him I use the quirky update expecting it to break at some point with service packs or new sql versions, but I use it in the meanwhile because it's fast and I would care about the problems when they arise.
He said that he would never step on a bridge built by an engineer that works this way....
Good point, actually.
The vast majority of bridges in this world are "built" by laying down a plank or two of wood from one side to the other (of whatever is being bridged). No engineering standards applied, nor any applicable.
Such bridges will break/wash away/rot given much time. They will then again cost just about $0 to replace, just like they did the first time.
If Joe would actually refuse to cross such a bridge, which is what he's claiming, then he's a complete fool. (He's not a fool. He's just fixed on certain ideas, and willing to back them up with silly hyperbole based on nothing but the usual emotional manipulation of such statements.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 12, 2009 at 7:11 am
jcrawf02 (10/9/2009)
Aside from the rioting that will occur when parts of the Thread no longer make sense ...[pause for effect]...
...
...
...I don't think any of us really care about generating points from this particular thread. Deduct points gained from this topic, but keep others?
The Thread makes sense????
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 8,491 through 8,505 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply