June 17, 2009 at 10:33 am
Paul White (6/17/2009)
Lynn Pettis (6/17/2009)
Flip the order of the tables in the trigger and the the update occurs properly based on the clustered index on the table being updated even with the force plan. So, it also comes down to the position of the tables in the FROM clause.Yep. I have been quite a proponent of the UPDATE method in the past, but I must admit I am now concerned. The list of requirements (the ones I can remember immediately) is now:
No parallelism
Correct clustered index
INDEX(0)
TABLOCKX - maybe
No loop join unless the join order is forced to drive from the table with the clustered index (triggers only?)
I wish Jeff were online!
edit:
Reversing the written order of the tables is not sufficient - one must also either use INNER LOOP JOIN or OPTION (FORCE ORDER, LOOP JOIN). The former produces a warning unless FORCE ORDER is also specified, and they both force the join order of all tables that might be specified - that might be awkward in some circumstances, for example with a sub-select in the SELECT list, or with an EXISTS caluse.
Ummmm.... my rule of thumb is to peel one potato at a time on the quirky update... no joins allowed on the quirky update (still doing a lot of testing in that area, though). The INDEX(0) thingy is just to give people the nice warm fuzzies and to keep some folks from trying to install hand rails on my hiney.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 10:35 am
GilaMonster (6/17/2009)
Paul White (6/17/2009)
Yep. I have been quite a proponent of the UPDATE method in the past, but I must admit I am now concerned. The list of requirements (the ones I can remember immediately) is now:No parallelism
Correct clustered index
INDEX(0)
TABLOCKX - maybe
No loop join unless the join order is forced to drive from the table with the clustered index (triggers only?)
And even then there's still no guarantee that it will work in the next hotfix, CU, SP or version.
But, that's true even with documented features. They can and have made such changes with no warning and no deprecation notice.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 10:39 am
GilaMonster (6/17/2009)
Paul White (6/17/2009)
Yep. I have been quite a proponent of the UPDATE method in the past, but I must admit I am now concerned. The list of requirements (the ones I can remember immediately) is now:No parallelism
Correct clustered index
INDEX(0)
TABLOCKX - maybe
No loop join unless the join order is forced to drive from the table with the clustered index (triggers only?)
And even then there's still no guarantee that it will work in the next hotfix, CU, SP or version.
Skip this post..
Current case...
For everybody who works with "Nullbuster" columns. This feature works no more for SQL Server 2008:
http://www.sqlservercentral.com/Forums/Topic735610-149-1.aspx
Flo
June 17, 2009 at 10:49 am
Forget my previous post!!!
Sorry!
June 17, 2009 at 1:47 pm
Paul White (6/17/2009)
GilaMonster (6/16/2009)
I just got word from the documentation people. The lack of the UPDATE <alias> was simply an oversight and it will be corrected in a future version of BoL.While I'm on the subject...
I understand it is just a documentation oversight - but if we commit ourselves to never using 'undocumented' syntax (Barry!), how should we write an UPDATE from a self-join without an alias?
Re-using the example I just posted:
--create table dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B](cost int, row_id int)
UPDATEdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
SETcost = 0
FROMdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
JOINdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B].row_id
[font="Courier New"]Msg 8154, Level 16, State 1, Line 1
The table 'dbo.976231EF-DA13-4A8A-91DC-7D8931A2C14B' is ambiguous.
[/font]
???
Like this, of course:
UPDATEdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
SETcost = 0
FROMdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
JOINdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B].row_id
Adn that's only one of the reasons that I try not to use GUIDs as table names! 😀
[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]
June 17, 2009 at 1:57 pm
RBarryYoung (6/17/2009)
Adn that's only one of the reasons that I try not to use GUIDs as table names! 😀
You mean there are other reason??????
..... when I told you to encrypt the data I did not mean for you to encrypt the object names also!!!!!!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 17, 2009 at 1:57 pm
Paul White (6/17/2009)
Lynn Pettis (6/17/2009)
Flip the order of the tables in the trigger and the the update occurs properly based on the clustered index on the table being updated even with the force plan. So, it also comes down to the position of the tables in the FROM clause.Yep. I have been quite a proponent of the UPDATE method in the past, but I must admit I am now concerned. The list of requirements (the ones I can remember immediately) is now:
No parallelism
Correct clustered index
INDEX(0)
TABLOCKX - maybe
No loop join unless the join order is forced to drive from the table with the clustered index (triggers only?)
I wish Jeff were online!
edit:
Reversing the written order of the tables is not sufficient - one must also either use INNER LOOP JOIN or OPTION (FORCE ORDER, LOOP JOIN). The former produces a warning unless FORCE ORDER is also specified, and they both force the join order of all tables that might be specified - that might be awkward in some circumstances, for example with a sub-select in the SELECT list, or with an EXISTS caluse.
AFAIK, the "Quirky Update" (or Update pseudocursor, as I prefer) is not and never was claimed to work with a JOIN. I certainly would never use it that way. (seems like i've had this discusssin before ... ?)
What I find most intresting about Paul's example is tha apparently, the inserted rows to the log file either get stored or retrieved in reverse order? Seem like there must be an intresting backstory to that behavior. 🙂
[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]
June 17, 2009 at 2:08 pm
RBarryYoung (6/17/2009)
What I find most intresting about Paul's example is tha apparently, the inserted rows to the log file either get stored or retrieved in reverse order? Seem like there must be an intresting backstory to that behavior. 🙂
In 2005/2008 (Which I assume is what Paul's using) the inserted/deleted tables are materialised from the row version store, not from the transaction log. They were materialised from the tran log in 2000.
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
June 17, 2009 at 2:08 pm
Oops, Gail already got the right UPDATE syntax. And Jeff already pointed out the JOIN thing. And Paul already mentioned the reverse order [inserted] business. .. 🙁
Trying to catch-up in the Thread at the end of the day makes me feel like a newspaper: All the news thats fit to print, a day later. 😛
[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]
June 17, 2009 at 2:23 pm
GilaMonster (6/17/2009)
RBarryYoung (6/17/2009)
What I find most intresting about Paul's example is tha apparently, the inserted rows to the log file either get stored or retrieved in reverse order? Seem like there must be an intresting backstory to that behavior. 🙂In 2005/2008 (Which I assume is what Paul's using) the inserted/deleted tables are materialised from the row version store, not from the transaction log. They were materialised from the tran log in 2000.
Right. I keep forgetting that all databases have row versioning, even if they don't turn Snapshots on.
[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]
June 17, 2009 at 3:00 pm
I used both 2005 and 2008 - though the posted execution plan was from my 2005 instance.
The fascinating thing, as Barry remarked, is the rows-in-reverse-order thing. I also assumed it was to do with the way rows are retrieved from the version store, both it would be great to understand more about that - even if the information is entirely useless 🙂
I guess my main concern was that people might try to adopt the UPDATE running total idea into a trigger to maintain real-time running totals for a table (it was Aaron's recent problem that got me into trying this). I will be sure to emphasize the potential issues concerning joins to anyone I come across trying this sort of thing.
BTW, until SQL Server includes some sort of RUNNING_TOTAL() [OVER()] syntax, the UPDATE method remains a reliable and fast way to compute and store running totals in the right circumstances.
I guess I was just a bit shocked by the lack of a sort in the query plan I posted to ensure updates were in clustered-index order. It had not occurred to me that rows which happened to be sorted in the reversed order would not ensure the QO added a sort. That, in a nutshell, is probably my point.
Stand down the handrail installation team. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 3:50 pm
Paul White (6/17/2009)
I used both 2005 and 2008 - though the posted execution plan was from my 2005 instance.The fascinating thing, as Barry remarked, is the rows-in-reverse-order thing. I also assumed it was to do with the way rows are retrieved from the version store, both it would be great to understand more about that - even if the information is entirely useless 🙂
I guess my main concern was that people might try to adopt the UPDATE running total idea into a trigger to maintain real-time running totals for a table (it was Aaron's recent problem that got me into trying this). I will be sure to emphasize the potential issues concerning joins to anyone I come across trying this sort of thing.
BTW, until SQL Server includes some sort of RUNNING_TOTAL() [OVER()] syntax, the UPDATE method remains a reliable and fast way to compute and store running totals in the right circumstances.
I guess I was just a bit shocked by the lack of a sort in the query plan I posted to ensure updates were in clustered-index order. It had not occurred to me that rows which happened to be sorted in the reversed order would not ensure the QO added a sort. That, in a nutshell, is probably my point.
Stand down the handrail installation team. 🙂
All they really need to do is make SUM() OVER work according to ANSI standards (or so I've heard). Of course, that particular documented feature change will break the hell out of a lot of folks code when they do. 😛 Not likely they'll wait for a couple of cycles and a deprecation notice for that one, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 4:18 pm
Jeff Moden (6/17/2009)
All they really need to do is make SUM() OVER work according to ANSI standards (or so I've heard). Of course, that particular documented feature change will break the hell out of a lot of folks code when they do. 😛 Not likely they'll wait for a couple of cycles and a deprecation notice for that one, either.
Ha!
So how would the 'standard' solution work? Would PARTITION BY do the running totals using the ORDER BY part of the statement to define the cumulative-sum order?
Personally I think I'd prefer a new window aggregate - if only to get a better name!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 4:22 pm
For the Anything that is NOT about SQL! forum, this thread has gotten a bit technical. I'm used to the light-hearted bits.
Time for a nice Merlot. 😀 as the number of updates you can get on a pinhead has left me in a bit of a spin. :crazy:
June 17, 2009 at 5:07 pm
Paul White (6/17/2009)
I understand it is just a documentation oversight - but if we commit ourselves to never using 'undocumented' syntax (Barry!), how should we write an UPDATE from a self-join without an alias?
[font="Verdana"]The same way you do in Oracle, which doesn't have the additional FROM clause for UPDATE: you use a CTE to do the join, and then UPDATE the result of the CTE.[/font]
Viewing 15 posts - 5,806 through 5,820 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply