Dynamics Community Forum Thread Details

tr_ItemStoreQty cause System hang, Post batched Hang, Headquater Server Hang. (Urgent)

Hi All

I really some help on this case

My customer ItemDynamic Table very Huge 300K item * 120 Store.

if everytime update this ItemDynamic Table it trigger this will cause all system hang. headquater server hang, a lot screen hang.

Is it possible i remove this trigger to increase the speed?

or any solution for this case?

CREATE TRIGGER [dbo].[tr_ItemStoreQty]

ON [dbo].[ItemDynamic] FOR UPDATE, INSERT, DELETE AS

/* Updates Quantity and QuantityCommitted in Item table when changes are made to the ItemDynamic table */

DECLARE @Rows INT

SELECT @Rows = @@ROWCOUNT

IF @Rows = 0

/* No rows inserted or deleted, exit trigger */

RETURN

/* Update the Quantity and QuantityCommitted fields in ItemDynamic table */

SELECT ItemDynamic.ItemID,

SUM(ItemDynamic.Quantity) AS Quantity,

SUM(ItemDynamic.QuantityCommitted) AS QuantityCommitted

INTO #tr_Temp

FROM ItemDynamic

WHERE ItemDynamic.ItemID IN

(

SELECT ItemID

FROM INSERTED

UNION

SELECT ItemID

FROM DELETED

)

GROUP BY ItemDynamic.ItemID

UPDATE Item

SET Quantity = #tr_Temp.Quantity,

QuantityCommitted = #Tr_temp.QuantityCommitted

FROM Item, #tr_Temp

WHERE Item.ID = #tr_Temp.ItemID

Thank You for any help and suggestion.

Hi NG,

We are having 97172293 records in item dynamic table and we don't face such issues. You should never remove tigers in HQ tables unless MS advice to do so. As you can see in this tigger, it does update item table. As jef said, they added index in item dynamic in FP2 to increase the 401 data transfer performance . I'm not sure what version your client in. You may check the following to start troubleshooting the issue

> Network connectivity speed between store and HQ.

> Number of item getting upload to HQ daily

> Worksheet connection schedule

Possible Solutions

> Increase the number of HQ server applications . This can be done in same server if you have a high end one

> Change the schedule to connect to HQ server application not more than 2 shops at a time. If you have 6 HQ

server application, then make 2 shops connect to one HQ application at a time. Keep at least 20 min gap for

next connectivity. (Again this depend on the connectivity speed between HQ-Store

> If your client having slow connectivity, do recommend to update the the link speed.

Hope this will help

Regards

Quick Links