Post reply

Locking Perf Problem: Help Update/Insert WITH table_hints

  • June 12, 2009 at 3:29 pm

    #205466

    I have 50 jobs recurring every 10 seconds to concurrently call the same stored procedure to update a 5000 row table, 100 unique rows at a time. The table never grows or shrinks.

    Let's say the target table to be updated is a CurrentPurchases table, and each row represents the last purchase of a distinct product type.

    There is a trigger attached "FOR AFTER UPDATE" that handles "running totals" logic, and adds calculations based off of "inserted" and "deleted" rowsets from the old data before the update and the new data after the update for each row.

    All together, each of the 50 jobs run concurrently every 10 seconds to call this stored procedure that gets the 100 new rows for the 100 rows to be updated in the CurrentPurchases table. The update from this stored procedure subsequently triggers the FOR AFTER UPDATE trigger to perform time-slice calculations and running totals calculations.

    Prior to adding the trigger, this mechanism only took 1.5 seconds per rowset update. Now, it takes an average of 50 seconds.

    I beleive the problem is that due to concurrency and locking mechanisms, the table is being locked before allowing the next distinct set of product purchases (transactions) to be executed.

    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), 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)

    How do I add a "WITH " to stop the locking mechanisms? Because the trigger is used, I don't really care for 100% accuracy, as long as the insert INTO dbo.Purchases happens, ordering isn't an issue. I don't mind separating the OUTPUT (INSERT INTO dbo.Purchases) into an INSERT clause with a "WITH " clause if this is the way to remove appropriate writing locks. The order is unimportant to me, no data is accessed more than just the single write to the Purchases table or the block of row updates in the CurrentPurchases table each 10 seconds during this data capture mechanism. Because the PurchaseDateTime is used, I can later reorder the table if things get out of order. But the locks are crippling the system (my assumption). I may be wrong, this may be the nature of triggers, too. Any help is appreciated.

    I will create a complete code example after hours if necessary, but it is complicated to represent the entire system out of it's current context, and may be non-pertinent to the UPDATE (and/or INSERT) locking issue.

    Thanks,

    Aaron

  • June 12, 2009 at 10:53 pm

    #1009005

    Cross post... please, no answers here. Go to the following instead...

    http://www.sqlservercentral.com/Forums/Topic734177-338-1.aspx

    --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)

Viewing 2 posts - 1 through 1 (of 1 total)

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