June 17, 2009 at 1:11 am
Jeff Moden (6/16/2009)
Now, if we could just get them to say that UPDATEs always work in clustered index order... 😛
They won't. The unofficial word from one of the guys who works on the optimiser is that there is nothing that enforces update order. If that's true then the fact that it currently does always1 update in clustered index order is just a side-effect of other behaviour.
(1): In all cases that have been observed.
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 5:38 am
GilaMonster (6/17/2009)
In all cases that have been observed.
Maybe not 😉
USE tempdb;
GO
CREATE TABLE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
(
row_idINTIDENTITY(1,1)PRIMARY KEY CLUSTERED,
costINTNOT NULL,
running_totalINTNOT NULL DEFAULT 0
);
GO
CREATE TRIGGER [trg_976231EF-DA13-4A8A-91DC-7D8931A2C14B_IOI]
ON dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
AFTER INSERT
AS
BEGIN
IF@@ROWCOUNT = 0 RETURN;
SETROWCOUNT 0;
SETNOCOUNT ON;
DECLARE@total INT;
SET@total = 0;
UPDATETest
SET@total = Test.running_total = Test.cost + @total
FROMinserted I
JOINdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS Test
ON (Test.row_id = I.row_id);
END;
GO
INSERTdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] (cost)
SELECTTOP (10)
FLOOR(RAND(CHECKSUM(NEWID())) * 1000)
FROMmaster.sys.all_columns C1
CROSS
JOINmaster.sys.all_columns C2
GO
SELECTrow_id, cost, running_total
FROMdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
ORDERBY
row_id;
GO
DROP TABLE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B];
Results:
[font="Courier New"]row_idcostrunning_total
14595254
27904795
32024005
48053803
52882998
64372710
77772273
89501496
9273546
10273273[/font]
Actual execution plan attached. :w00t:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 6:01 am
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:
UPDATEdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
SETcost = 0
FROMdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS A
JOINdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = A.row_id
[font="Courier New"]Msg 8154, Level 16, State 1, Line 1
The table 'dbo.976231EF-DA13-4A8A-91DC-7D8931A2C14B' is ambiguous.
[/font]
UPDATEA
SETcost = 0
FROMdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS A
JOINdbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = A.row_id
[font="Courier New"](10 row(s) affected)[/font]
🙂
edit: reworded to hopefully emphasize the humour.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 6:25 am
Paul,
Check out the difference between the two pieces of code below and their behaviour:
CREATE TABLE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
(
row_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
cost INT NOT NULL,
running_total INT NOT NULL DEFAULT 0
);
GO
CREATE TRIGGER [trg_976231EF-DA13-4A8A-91DC-7D8931A2C14B_IOI]
ON dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
AFTER INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET ROWCOUNT 0;
SET NOCOUNT ON;
DECLARE @total INT;
SET @total = 0;
UPDATE Test SET
@total = Test.running_total = Test.cost + @total
FROM
inserted I
INNER JOIN dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS Test
ON (Test.row_id = I.row_id);
END;
GO
INSERT dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] (cost)
SELECT TOP (10)
FLOOR(RAND(CHECKSUM(NEWID())) * 1000)
FROM master.sys.all_columns C1
CROSS
JOIN master.sys.all_columns C2
GO
SELECT row_id, cost, running_total
FROM dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
ORDER BY
row_id;
GO
DROP TABLE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B];
GO
CREATE TABLE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
(
row_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
cost INT NOT NULL,
running_total INT NOT NULL DEFAULT 0
);
GO
CREATE TRIGGER [trg_976231EF-DA13-4A8A-91DC-7D8931A2C14B_IOI]
ON dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
AFTER INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET ROWCOUNT 0;
SET NOCOUNT ON;
DECLARE @total INT;
SET @total = 0;
UPDATE Test SET
@total = Test.running_total = Test.cost + @total
FROM
inserted I
INNER JOIN dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS Test WITH (INDEX(0),TABLOCKX)
ON (Test.row_id = I.row_id);
END;
GO
INSERT dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] (cost)
SELECT TOP (10)
FLOOR(RAND(CHECKSUM(NEWID())) * 1000)
FROM master.sys.all_columns C1
CROSS
JOIN master.sys.all_columns C2
GO
SELECT row_id, cost, running_total
FROM dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
ORDER BY
row_id;
GO
DROP TABLE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B];
GO
June 17, 2009 at 6:35 am
Lynn,
Well yes, quite. The previous discussion on this emphasized that INDEX(0) was not required.
Indeed, it is still not sufficient, try:
INNER LOOP JOIN dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS Test WITH (INDEX(0),TABLOCKX)
The rows seem to 'naturally' come out of the inserted pseudo-table in reverse clustered index order - this was the behaviour I had not seen before.
Of course one could also drive the query from Test by reversing the join and so on, but...
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 6:44 am
Ooo! Just found a better way to break INDEX(0):
SET FORCEPLAN ON
INSERT dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] (cost)
SELECT TOP (10)
FLOOR(RAND(CHECKSUM(NEWID())) * 1000)
FROM master.sys.all_columns C1
CROSS
JOIN master.sys.all_columns C2
GO
That produces the loop join and reverse-ordering, without changing the trigger. Ouch!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 6:55 am
However, you are forcing the order of the update by INSERTED, not the table you are updating. So, are you really breaking the quirky update that allows for a one million row update to process in about ten seconds?
I'd really like to hear what Jeff has to say on this aspect.
June 17, 2009 at 7:02 am
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.
June 17, 2009 at 7:09 am
Hehe, some are really mind-boggling
June 17, 2009 at 7:19 am
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:
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 7:28 am
Paul White (6/17/2009)
GilaMonster (6/17/2009)
In all cases that have been observed.Maybe not 😉
Weren't you the one who said earlier in this thread that you'd never seen an update that didn't work in clustered index order?
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:
UPDATE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]SET cost = 0
FROM dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS A
JOIN dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = A.row_id
Msg 8154, Level 16, State 1, Line 1
The table 'dbo.976231EF-DA13-4A8A-91DC-7D8931A2C14B' is ambiguous.
As per books online:
If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.
This works just fine
UPDATE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
SET cost = 0
FROM dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
JOIN dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B].row_id
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 7:31 am
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.
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 7:34 am
GilaMonster (6/17/2009)
Weren't you the one who said earlier in this thread that you'd never seen an update that didn't work in clustered index order?
Yes indeed. And that was rather my point. I have now! Posted it as soon as I came across it. Quite shocked I was!
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?
GilaMonster (6/17/2009)
This works just fine
UPDATE dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]SET cost = 0
FROM dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B]
JOIN dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B] AS B ON B.row_id = dbo.[976231EF-DA13-4A8A-91DC-7D8931A2C14B].row_id
Oh yes of course. Not as pretty maybe, but it works, so... 😎
Apologies to Barry :blush:
edit: several, for wording, to fix an unclosed quote, and well stuff really
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 7:54 am
GilaMonster (6/17/2009)
And even then there's still no guarantee that it will work in the next hotfix, CU, SP or version.
Oh I wouldn't worry about that.
The good news is that we can all now write code that depends on rows coming out of the inserted and deleted 'tables' in reverse-clustered index order! 😀 😀 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 17, 2009 at 10:26 am
GilaMonster (6/17/2009)
Jeff Moden (6/16/2009)
Now, if we could just get them to say that UPDATEs always work in clustered index order... 😛They won't. The unofficial word from one of the guys who works on the optimiser is that there is nothing that enforces update order. If that's true then the fact that it currently does always1 update in clustered index order is just a side-effect of other behaviour.
(1): In all cases that have been observed.
Yeppp. Agreed. Just wishful thinking on my part. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 5,791 through 5,805 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply