June 12, 2009 at 4:27 pm
System:
I have 50 jobs recurring every 10 seconds to concurrently update a 5000 row table called CurrentPurchases. Each job updates the same 100 rows at a time, no job updates rows associated with another job. The table never grows or shrinks.
Each row in the CurrentPurchases table represents the last purchase of a distinct ProductTypeID.
There is a trigger attached "FOR AFTER UPDATE" that handles "running totals" logic, and adds calculations utilizing the built-in "inserted" and "deleted" tables within the trigger. It uses inserted.PurchaseDateTime - deleted.PurchaseDateTime to get the time difference between purchases, and it sets CurrentPurchase.RunningTotalCost = deleted.RunningTotalCost + inserted.Cost to get the total amount of sales for each updated product.
Problem:
Prior to adding the trigger, this mechanism only took 1.5 seconds per rowset update. Now, it takes an average of 50 seconds per rowset update, yet varies in range from 1.8 seconds to 70 seconds. I need the update (or insert) to happen less than every 10 seconds again to match the recurrence cycle of the jobs.
I beleive the problem is due to concurrency and locking mechanisms (transactions, row locking, table locking, etc), i.e. the table is being locked or the next transaction is being queued before allowing the update of the next distinct set of purchases in the CurrentPurchases table. This is HORRIBLE for concurrent disjoint updates!
Question:
How do I use the following WITH a table_hint to optimize for concurrency, disregarding locks (I'm sure that in 10 seconds the update transaction can be completed without the dependency on the unrelated updates happening at the same time). How do I add a "WITH " to stop the locking mechanisms or to allow the transactions to be concurrent?
Problematic Trigger Code:
Here is the update used for calculations in the FOR AFTER UPDATE trigger:
--(FOR AFTER UPDATE trigger ON dbo.CurrentPurchases)
UPDATE dbo.CurrentPurchases
SET
dbo.CurrentPurchases.RunningTotalCost = deleted.RunningTotalCost + inserted.Cost,
dbo.CurrentPurchases.PurchaseDateTime = DATEDIFF(ms, deleted.PurchaseDateTime, inserted.PurchaseDateTime)
OUTPUT
deleted.ProductTypeID,
deleted.Cost,
deleted.PurchaseDateTime
INTO dbo.Purchases
FROM inserted
INNER JOIN deleted ON deleted.ProductTypeID = inserted.ProductTypeID
--(End trigger)
Thanks,
Aaron
June 12, 2009 at 10:45 pm
This is a classic error that causes huge performance problems. The target of the update must also be included in the FROM clause if a join is present. It doesn't always cause a problem when missing, but when it does, it's a huge problem. It causes the whole thing to act like a cursor with a recomplile for every row if you don't include the target table in the FROM clause with the appropriate joins in the FROM clause.
Trust me on this... I've seen it a dozen times.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2009 at 12:50 am
Jeff,
Sorry for the cross-post, wasn't sure if this was a performance or tsql question.
THANK YOU!!! OMG! That is so obscure. It's driving me NUTS. I've been reasearching isolation levels, locking, all sorts of junk. Speaking of, since I will never read from this table in any way pertinent... and phantoms, lost data, etc, I don't care about all of that, esp since the trigger handles the logic for inserting into the correct table with the correct data:
Is there a way I can increase performance for concurrency by making the database's isolation level to "READ UNCOMMITTED"? Or is there a way to do this just for the CurrentPurchases and Purchases tables?
Is this updated code along the lines of what you were talking about (adding the CurrentPurchases table reference in the FROM field instead of "inserted"):
--(FOR AFTER UPDATE trigger ON dbo.CurrentPurchases)
UPDATE dbo.CurrentPurchases
SET
dbo.CurrentPurchases.RunningTotalCost = Old.RunningTotalCost + Cur.Cost,
dbo.CurrentPurchases.PurchaseDateTime = DATEDIFF(ms, Old.PurchaseDateTime, Cur.PurchaseDateTime)
OUTPUT
Old.ProductTypeID,
Old.Cost,
Old.PurchaseDateTime
INTO dbo.Purchases
FROM dbo.CurrentPurchases Cur
INNER JOIN deleted Old ON Old.ProductTypeID = Cur.ProductTypeID
--(End trigger)
I'll add a response after I can get some production data squirted into this system starting Monday.
Thanks again!
Aaron
June 13, 2009 at 1:31 am
I would usually write it like this:
--(FOR AFTER UPDATE trigger ON dbo.CurrentPurchases)
UPDATECur
SETdbo.CurrentPurchases.RunningTotalCost = Old.RunningTotalCost + Cur.Cost,
dbo.CurrentPurchases.PurchaseDateTime = DATEDIFF(ms, Old.PurchaseDateTime, Cur.PurchaseDateTime)
OUTPUT
Old.ProductTypeID,
Old.Cost,
Old.PurchaseDateTime
INTOdbo.Purchases
FROMdbo.CurrentPurchases Cur
JOINinserted ON inserted.ProductTypeID = Cur. ProductTypeID
JOINdeleted Old ON Old.ProductTypeID = Cur.ProductTypeID
--(End trigger)
...assuming that ProductTypeID is a primary/unique key for the CurrentPurchases table.
It may just be a question of style in this particular case, but using the alias and including the extra join to inserted make it clearer to my mind, and also ensures that this only ever applies to updates, even if the trigger is later modifed to work with inserts or deletes.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 13, 2009 at 2:29 am
I spent some time writing a complete example (excluding the concurrency and jobs) for this. Please feel free to try out this code to play around with it. ALSO, my ISOLATION LEVEL question is still oustanding for the concurrency.
**** Can I set READ UNCOMMITTED to write asynchronously to thie "buffer" (current) table?
I'm providing this code as a GEM to the community to prod you all to answer my concurrency issue. 🙂
--==== Date: 20090613_0320
--==== Author: Aaron Elliott / DIS. aaron.elliott@defenseinfosec.com
--==== Description: This example demonstrates sort of a buffer table to hold
--==== current data to perform rapid calculations on using updates
--==== and a FOR AFTER UPDATE trigger. This includes time-slice
--==== calculations, and running totals. This example only works
--==== if the buffer table (TestCurrentPurchases table) only has
--==== one row to represent each distinct type (ProductTypeID)
--==== that can go into the historic table (TestPurchases table).
--====
--==== Output: Three tables:
--==== 1. Buffer table with all current raw data, differential calculations, and running totals.
--==== 2. Historical table after all inserts / updates, with raw data, and differential calculations.
--==== 3. Historical table indexed / physically clustered by type and date.
--====
--==== Key words: Running totals, time-slices, real-time calculations, update
--==== trigger, output into, very large tables, clustered index.
--==== 1. Reset the environment if error tables weren't deleted upon completion.
-- Delete the TestPurchases table if it exists.
IF EXISTS (SELECT name FROM sysobjects WHERE xtype='u' AND name = 'TestPurchases')
DROP TABLE dbo.TestPurchases;
GO
-- Delete the TestCurrentPurchases table if it exists.
IF EXISTS (SELECT name FROM sysobjects WHERE xtype='u' AND name = 'TestCurrentPurchases')
DROP TABLE dbo.TestCurrentPurchases;
GO
--==== 2. Creates a TestPurchases table with a nonclustered primary key index on ID
-- The TestPurchases table will hold a log of all purchases, except the current purchase.
CREATE TABLE dbo.TestPurchases
(
PurchaseID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
ProductTypeID INT NOT NULL,
PurchaseDateTime DATETIME NOT NULL,
Cost MONEY NULL,
CostDifference MONEY NULL,
TimeBetweenPurchasesOfProductType INT NULL
);
--==== 3. Creates a TestCurrentPurchases table with a clustered primary key index on CurrentPurchaseID.
-- This table has a row for each ProductTypeID (1:1 relationship). Each row contains calculations
-- of new purchase data on old purchase data, like cost difference, time in ms since last purchase,
-- and a running total of the cost of all purchases for that product type.
CREATE TABLE dbo.TestCurrentPurchases
(
CurrentPurchaseID INT IDENTITY(1,1) PRIMARY KEY,
ProductTypeID INT NOT NULL,
PurchaseDateTime DATETIME NOT NULL,
Cost MONEY NULL,
CostDifference MONEY NULL,
TimeBetweenPurchasesOfProductType INT NULL,
RunningTotalCost MONEY NULL
);
GO
--==== 3. Inserts some raw sample data (without calculations) into the TestCurrentPurchases table.
INSERT INTO dbo.TestCurrentPurchases (ProductTypeID, PurchaseDateTime, Cost)
SELECT 1, '6/1/2009 11:40:00.000', 1.50
UNION
SELECT 2, '6/1/2009 11:47:00.000', 2.75
UNION
SELECT 3, '6/1/2009 11:51:00.000', 15.39
UNION
SELECT 4, '6/1/2009 12:11:00.000', 0.79
UNION
SELECT 5, '6/1/2009 13:39:00.000', 82.49
UNION
SELECT 6, '6/1/2009 17:39:00.000', 16.00;
GO
--====4. Create the FOR AFTER UPDATE trigger to perform old/new data calculations, and running totals.
CREATE TRIGGER trUpdateTestCurrentPurchases
ON dbo.TestCurrentPurchases
AFTER UPDATE
AS
BEGIN
-- This update statement performs differential calculations, and running total calculations. It also
-- outputs the previous row of raw data with differential calculations to the TestPurchases table for
-- historical, reporting, and analysis purposes. Running total information is kept in the
-- TestCurrentPurchase table and is used for real-time running total analysis.
UPDATE dbo.TestCurrentPurchases
SET
CostDifference = ISNULL(Cur.Cost, 0) - ISNULL(Old.Cost, 0),
TimeBetweenPurchasesOfProductType = DATEDIFF(ms, Old.PurchaseDateTime, Cur.PurchaseDateTime),
RunningTotalCost = ISNULL(Old.RunningTotalCost, 0) + Cur.Cost
OUTPUT
Old.ProductTypeID,
Old.PurchaseDateTime,
Old.Cost,
ISNULL(Old.CostDifference, 0),
ISNULL(Old.TimeBetweenPurchasesOfProductType, 0)
INTO dbo.TestPurchases
FROM dbo.TestCurrentPurchases Cur
INNER JOIN deleted Old ON Old.ProductTypeID = Cur.ProductTypeID
END;
GO
--====5. Update the TestCurrentPurchases table with some raw data, performing 4 total sample updates (below).
-- Update (with trigger) #1.
UPDATE dbo.TestCurrentPurchases
SET
ProductTypeID = Src.ProductTypeID,
PurchaseDateTime = Src.PurchaseDateTime,
Cost = Src.Cost
FROM dbo.TestCurrentPurchases Trg
INNER JOIN
(
SELECT 1 [ProductTypeID], '6/2/2009 11:40:00.000' [PurchaseDateTime], 2.50 [Cost]
UNION
SELECT 2 [ProductTypeID], '6/2/2009 11:47:00.000' [PurchaseDateTime], 1.75 [Cost]
UNION
SELECT 3 [ProductTypeID], '6/2/2009 11:51:00.000' [PurchaseDateTime], 13.39 [Cost]
UNION
SELECT 4 [ProductTypeID], '6/2/2009 12:11:00.000' [PurchaseDateTime], 0.69 [Cost]
UNION
SELECT 5 [ProductTypeID], '6/2/2009 13:39:00.000' [PurchaseDateTime], 83.49 [Cost]
UNION
SELECT 6 [ProductTypeID], '6/2/2009 17:39:00.000' [PurchaseDateTime], 16.00 [Cost]
) Src ON Src.ProductTypeID = Trg.ProductTypeID
GO
-- Update (with trigger) #2.
UPDATE dbo.TestCurrentPurchases
SET
ProductTypeID = Src.ProductTypeID,
PurchaseDateTime = Src.PurchaseDateTime,
Cost = Src.Cost
FROM dbo.TestCurrentPurchases Trg
INNER JOIN
(
SELECT 1 [ProductTypeID], '6/3/2009 11:40:00.000' [PurchaseDateTime], 3.50 [Cost]
UNION
SELECT 2 [ProductTypeID], '6/3/2009 11:47:00.000' [PurchaseDateTime], 1.76 [Cost]
UNION
SELECT 3 [ProductTypeID], '6/3/2009 11:51:00.000' [PurchaseDateTime], 11.39 [Cost]
UNION
SELECT 4 [ProductTypeID], '6/3/2009 12:11:00.000' [PurchaseDateTime], 0.39 [Cost]
UNION
SELECT 5 [ProductTypeID], '6/3/2009 13:39:00.000' [PurchaseDateTime], 103.89 [Cost]
UNION
SELECT 6 [ProductTypeID], '6/3/2009 17:39:00.000' [PurchaseDateTime], 6.00 [Cost]
) Src ON Src.ProductTypeID = Trg.ProductTypeID
GO
-- Update (with trigger) #3.
UPDATE dbo.TestCurrentPurchases
SET
ProductTypeID = Src.ProductTypeID,
PurchaseDateTime = Src.PurchaseDateTime,
Cost = Src.Cost
FROM dbo.TestCurrentPurchases Trg
INNER JOIN
(
SELECT 1 [ProductTypeID], '6/3/2009 12:40:00.000' [PurchaseDateTime], 12.50 [Cost]
UNION
SELECT 2 [ProductTypeID], '6/3/2009 11:49:00.000' [PurchaseDateTime], 11.75 [Cost]
UNION
SELECT 3 [ProductTypeID], '6/3/2009 11:51:50.000' [PurchaseDateTime], 113.39 [Cost]
UNION
SELECT 4 [ProductTypeID], '6/3/2009 16:11:00.000' [PurchaseDateTime], 10.69 [Cost]
UNION
SELECT 5 [ProductTypeID], '6/3/2009 15:39:00.000' [PurchaseDateTime], 183.49 [Cost]
UNION
SELECT 6 [ProductTypeID], '6/3/2009 18:40:01.000' [PurchaseDateTime], 116.00 [Cost]
) Src ON Src.ProductTypeID = Trg.ProductTypeID
GO
-- Update (with trigger) #4.
UPDATE dbo.TestCurrentPurchases
SET
ProductTypeID = Src.ProductTypeID,
PurchaseDateTime = Src.PurchaseDateTime,
Cost = Src.Cost
FROM dbo.TestCurrentPurchases Trg
INNER JOIN
(
SELECT 1 [ProductTypeID], '6/4/2009 13:40:00.000' [PurchaseDateTime], 12.50 [Cost]
UNION
SELECT 2 [ProductTypeID], '6/3/2009 16:01:00.000' [PurchaseDateTime], 11.75 [Cost]
UNION
SELECT 3 [ProductTypeID], '6/3/2009 11:54:50.000' [PurchaseDateTime], 113.39 [Cost]
UNION
SELECT 4 [ProductTypeID], '6/3/2009 16:12:00.000' [PurchaseDateTime], 10.69 [Cost]
UNION
SELECT 5 [ProductTypeID], '6/3/2009 15:50:00.000' [PurchaseDateTime], 183.49 [Cost]
UNION
SELECT 6 [ProductTypeID], '6/3/2009 19:47:01.000' [PurchaseDateTime], 116.00 [Cost]
) Src ON Src.ProductTypeID = Trg.ProductTypeID
GO
--====6. Display the results of the updates on the TestPurchases, and TestCurrentPurchases tables.
-- Notice this data has the correct running totals.
SELECT * FROM dbo.TestCurrentPurchases
GO
-- Notice the historical data contains not just the raw data, but the differential (time-slice) data too.
SELECT * FROM dbo.TestPurchases
--====7. Creates a clustered index on (reorganizes the data physically in order by)
--==== ProductTypeID then PurchaseDateTime. This is particularly important
--==== when the table grows to the 10's to 100's of millions of rows, and
--==== Gigabytes large. It speeds up ProductTypeID based analysis. If historical
--==== information is only necessary, the clustered index is not necessary. This is
--==== only included for completion.
CREATE CLUSTERED INDEX IX_ProductPurchaseDate ON dbo.TestPurchases (
ProductTypeID,
PurchaseDateTime
);
GO
--====8. Display the results of the updates on the TestPurchases table to show how
--==== the clustered index from 7 optimizes the data for retrieval on the
--==== ProductTypeID and PurchaseDateTime fields. Row-by-row comparison by type
--==== is much easier on the database resources, and the debugger's eye in this form.
SELECT * FROM dbo.TestPurchases
--==== 9. Remove the tables from the database and the trigger created.
DROP TABLE dbo.TestPurchases;
GO
DROP TABLE dbo.TestCurrentPurchases;
GO
Thanks,
Aaron Elliott
PS: Why go through all of the trouble creating this code and formatting it if it's just going to get hosed. I attached a text file with the formatting so it doesn't look as ugly as above.
June 13, 2009 at 4:51 am
Jeff Moden (6/12/2009)
It causes the whole thing to act like a cursor with a recomplile for every row if you don't include the target table in the FROM clause with the appropriate joins in the FROM clause.
Hey Jeff,
If the updated table isn't referenced in the FROM clause, it's worse than that isn't it?
The read-cursor part of the trigger update will join the inserted and deleted pseudo-tables just fine, but there will then be a cartesian join to the update target - meaning that every row of the target will get updated.
If inserted and deleted contain one row each, the target will be updated with the same value for every row.
If inserted and deleted contain multiple rows, the behaviour is undefined and depends on the plan selected.
Example script:
USE tempdb
GO
IFOBJECT_ID(N'dbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B]', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B];
END;
GO
CREATE TABLE dbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B]
(
row_idINTNOT NULLIDENTITY(1,1)PRIMARY KEY NONCLUSTERED,
dataINTNOT NULL,
totalINTNOT NULLDEFAULT 0,
);
GO
INSERTdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B] (data) VALUES (1);
INSERTdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B] (data) VALUES (2);
INSERTdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B] (data) VALUES (3);
INSERTdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B] (data) VALUES (4);
INSERTdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B] (data) VALUES (5);
GO
CREATE TRIGGER [dbo.DBB1B215-D46F-46FF-B223-B55B7EC5BA7B AU]
ONdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B]
AFTER UPDATE
AS
BEGIN
UPDATE[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B]
SETtotal = deleted.total + inserted.data
FROMinserted
JOINdeleted
ONdeleted.row_id = inserted.row_id
END
GO
SELECT*
FROMdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B];
GO
UPDATEdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B]
SETdata = data
WHERErow_id = 1;
GO
SELECT*
FROMdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B];
GO
UPDATEdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B]
SETdata = data
WHERErow_id > 1;
GO
SELECT*
FROMdbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B];
GO
DROP TABLE dbo.[DBB1B215-D46F-46FF-B223-B55B7EC5BA7B];
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 13, 2009 at 5:06 am
Aaron (6/13/2009)
**** Can I set READ UNCOMMITTED to write asynchronously to thie "buffer" (current) table?
The READ UNCOMMITTED isolation level only affects locking by selects. All data modifications will still lock. Not sure what you mean by 'asynchronously'. Explain your thoughts a bit more please?
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 13, 2009 at 6:01 am
Aaron,
Thanks for providing the example code. I had tried to create one myself, but was struggling with the datatype for the PurchaseDateTime column on the CurrentPurchases table, as it seemed to be both a datetime and an integer at the same time (from your originally posted code).
I would be interested to know whether you have observed locking contention or whether this is just a theory at present.
There are many options to improve concurrency in a database - but no magic bullets. The methods suitable for you will depend on exactly what problem you are seeing - in detail.
For example, if you are experiencing lock contention due to updates conflicting with read operations, you could consider using a row-versioning isolation level. This would have to be carefully considered and thoroughly tested - it is not always brilliantly suited to a high-update environment, and it's impact on tempdb can be significant. That said, it does do away with almost all shared locks. I would probably resist using READ UNCOMMITTED or NOLOCK hints.
I might also test using separate tables for each job process to minimize contention, with a view over them. Depending on the workload, it might be worth doing this for the both the update target and the insert target tables.
First off though, I would carefully review all the query plans involved to check for bad plan choices by the QO, missing indexes, and things of that sort.
Personally, I am not a big fan of using triggers in this way. Even with a careful design, performance can be a problem, as can recovering from an incident such as someone disabling the trigger by accident. If forced to use a trigger, I would consider an INSTEAD OF trigger rather than AFTER, since the two update operations on the same row could be collapsed into one. I am also not massively enamoured with the idea of using the OUTPUT clause to direct rows to a real table. That may just be personal preference though.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 13, 2009 at 9:10 am
That's good feedback. The two things you just mentioned not being a favorite of were actually suggestions from a previous post I originally wanted to avoid as well. The OUTPUT thing was just to make the example look a little more elegant. In my production code, I do have it split out into an INSERT statement. In fact, OUTPUT, by design, demonstrates additional locking issues I was hoping some would raise a flag on in providing more input on the concurrency issue.
Yes, the concurrency issue is theory. In my original post, above, (before I editted it a million times to clear it up) I actually said, "Unless this is the nature of triggers, in which I'm at a loss."
I really don't want to breakdown the logical normalisation of the tables just for efficiency. Then again, I've never dealt with a system with these two requirements: real-time calculations and running totals, and 50 million+ rows.
If push comes to shove, we can do everything in CLR or an external app, and only capture the raw data like we'd planned. It was just unbelievable that the performance can go from less than a second to 50 seconds, variant by almost 50 seconds... and it not be locking. My educated guess was to say, "No way a trigger multiplies latency by more than 100 times, this must be a concurrency thing."
I see above someone mentioned execution plan. I'll have to look at that, but I'm not a SQL PERF expert xor a CONCURRENCY expert, so these things may get tricky for me. I have to adapt the code to production to get the same "hugeness", but after toying with it and my example code from above (including all of your inputs), I can see how to do this a little. I have to go, but when I get back, I'll try this perf testing out on prod stuff and add another reply.
Thank you all for your inputs.
PS: 2 things, Paul, that's slick how you created the trigger for a temp table, I was wondering how to make an example without blowing away someone's "Purchases" table in prod, haha. #2, on top of ISOLATION LEVEL, LOCKS, etc... you said which execution plan it selects, which just reminded me, isn't there a hint to NOT RECOMPILE each time, would that help if it IS a concurrency issue?
June 13, 2009 at 9:45 am
Aaron (6/13/2009)
Is there a way I can increase performance for concurrency by making the database's isolation level to "READ UNCOMMITTED"? Or is there a way to do this just for the CurrentPurchases and Purchases tables?Is this updated code along the lines of what you were talking about (adding the CurrentPurchases table reference in the FROM field instead of "inserted"):
You can include the rows from the INSERTED table in a JOIN if needed, as well. The key is that the target table must also be included in the JOIN on an UPDATE if a JOIN is present.
I just quickly scanned what the others said and I'll have to dig into it a bit more, but as Gail said, READ UNCOMMITTED only affects reads. UPDATEs will always lock some portion of or, if escalation occurs, the whole table. Some concurrency optimization can be done with the UPDLOCK on UPDATEs. Lookup Table, Hints in BOL for more info on the subject.
Having said that, I need to read what the others have said on this thread. Gail is extremely sharp in matters of locking. The important thing that I wanted to get across is the target table in the from clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2009 at 9:48 am
Aaron,
The post Paul White wrote that starts with "I would usually write it like this:" is more of what I would have done and for the same reasons. That should wittle on your huge latency problem quite a bit.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2009 at 9:53 am
Aaron (6/13/2009)
PS: Why go through all of the trouble creating this code and formatting it if it's just going to get hosed. I attached a text file with the formatting so it doesn't look as ugly as above.
BWAA-HAAA!!!! I've been bitchin' to management about that very same problem for months, now. I thought they'd fix it when I said "They either seem unwilling or unable to make it work correctly" but to no avail. I think they have it setup to work nice and pretty with FireFox, but not IE.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2009 at 9:57 am
Paul White (6/13/2009)
Jeff Moden (6/12/2009)
It causes the whole thing to act like a cursor with a recomplile for every row if you don't include the target table in the FROM clause with the appropriate joins in the FROM clause.Hey Jeff,
If the updated table isn't referenced in the FROM clause, it's worse than that isn't it?
The read-cursor part of the trigger update will join the inserted and deleted pseudo-tables just fine, but there will then be a cartesian join to the update target - meaning that every row of the target will get updated.
If inserted and deleted contain one row each, the target will be updated with the same value for every row.
If inserted and deleted contain multiple rows, the behaviour is undefined and depends on the plan selected.
That sounds pretty much like what happens. I was being lazy and short just to get the point across about the target of the UPDATE needing to be in the FROM clause in the presence of a join.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2009 at 10:07 am
Aaron (6/13/2009)
That's good feedback.
Heh... I've found that many concurrency issues simply vanish in the presence of high speed code. What I'd be interested in is how fast the trigger runs after you fix the target table in the from clause issue. In most cases like this that I've had people fix, the code suddenly starts running in milliseconds and the concurrency issues are resolved simply by lack of long term interferance. If the case where the trigger code still has some minor latency issues, some proper index tuning using squares the remainder of the problem away.
I don't believe that testing on a half dozen rows will do much for you, either. Execution plans can change dramatically in the face of scalability. Lemme see if I can come up with the code to gen a million row test table.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2009 at 10:21 am
Jeff Moden (6/13/2009)
Heh... I've found that many concurrency issues simply vanish in the presence of high speed code.
Agreed. If the queries are running faster, they're locking for shorter periods and often locking fewer rows/pages. I've more than once seen one or two queries get optimised and suddenly blocking's virtually gone.
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
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply