Locking Perf Problem: Help Update/Insert WITH table_hints

  • 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

  • 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


    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)

  • 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

  • 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

  • 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.

  • 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];

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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?

  • 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


    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)

  • 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


    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)

  • 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


    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)

  • 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


    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)

  • 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


    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)

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 44 total)

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