Sage 500+Scanco+Intermec-CN3

Question

Hello Sage City, 
 
 My company currently uses Sage 500 ERP 2014 January Update (Version 7.60.5.0) with Scanco Warehouse Automation on Intermec CN3 devices. The issue we are having is extreme waits for pulling and updating picklists. A picklist with 10-12 items takes 12+ seconds just to load; one with 58 items took over 11 minutes. Updating is unuseably slow as well. 
 I have been working with Scanco, my Sage vendor, and my datacenter to try and resolve this with very limited improvements in performance. 
 My question: 
 Have any of you experienced a similar issue and what did you do to resolve it? 
 I know you may have questions and I have data so if you need info/clarification, just ask. 
 
 Thanks

LouDavis · Answer

I was looking at the mod we did for this issue and it also was due to a large number of records in timInvtTranDist, timInvtTranCost and timCostTier. What we found were the following statements in tI_timInvtran were doing an index scan of all records for each record being inserted. On large tables this was effectively a table scan...which since it is an insert will lock the table until it is done. -- Check that the ItemKey matches the distribution ItemKey. if exists (select 1 from inserted it join timInvtTranDist itd WITH (NOLOCK) on it.InvtTranKey = itd.InvtTranKey where it.ItemKey <> itd.ItemKey) begin select @errno = 50008, @errparam1 = 'INSERT', @errparam2 = 'ItemKey', @errparam3 = 'timInvtTranDist.ItemKey' raiserror (@errno, 16, 1, @errparam1, @errparam2, @errparam3) end -- Check that the WhseKey matches the distribution WhseKey. if exists (select 1 from inserted it join timInvtTranDist itd WITH (NOLOCK) on it.InvtTranKey = itd.InvtTranKey where it.WhseKey <> itd.WhseKey) begin select @errno = 50008, @errparam1 = 'INSERT', @errparam2 = 'WhseKey', @errparam3 = 'timInvtTranDist.WhseKey' raiserror (@errno, 16, 1, @errparam1, @errparam2, @errparam3) end -- Check that the ItemKey matches the cost tier ItemKey. -if exists (select 1 from inserted it join timInvtTranCost itc WITH (NOLOCK) on it.InvtTranKey = itc.InvtTranKey join timCostTier ct WITH (NOLOCK) on itc.CostTierKey = ct.CostTierKey where it.ItemKey <> ct.ItemKey) begin select @errno = 50008, @errparam1 = 'INSERT', @errparam2 = 'ItemKey', @errparam3 = 'timCostTier.ItemKey' raiserror (@errno, 16, 1, @errparam1, @errparam2, @errparam3) end if exists (select 1 from inserted it join timInvtTranCost itc WITH (NOLOCK) on it.InvtTranKey = itc.InvtTranKey join timCostTier ct WITH (NOLOCK) on itc.CostTierKey = ct.CostTierKey where it.WhseKey <> ct.WhseKey) begin select @errno = 50008, @errparam1 = 'INSERT', @errparam2 = 'WhseKey', @errparam3 = 'timCostTier.WhseKey' raiserror (@errno, 16, 1, @errparam1, @errparam2, @errparam3) end How we worked to around the performance issue was to preload the records we needed to work with into table variables and modified the statements to use those as the following example shows: DECLARE @_lDistMatch Table ( InvtTranKey INTEGER ,ItemKey INTEGER ,WhseKey INTEGER ); INSERT INTO @_lDistMatch (InvtTranKey, ItemKey, WhseKey) SELECT itd.InvtTranKey, itd.ItemKey, itd.WhseKey FROM inserted it inner join timInvtTranDist itd WITH (NOLOCK) on it.InvtTranKey = itd.InvtTranKey -- Check that the ItemKey matches the distribution ItemKey. if exists (select 1 from inserted it join @_lDistMatch itd on it.InvtTranKey = itd.InvtTranKey where it.ItemKey <> itd.ItemKey) begin select @errno = 50008, @errparam1 = 'INSERT', @errparam2 = 'ItemKey', @errparam3 = 'timInvtTranDist.ItemKey' raiserror (@errno, 16, 1, @errparam1, @errparam2, @errparam3) end As the table variables will only have those records associated to the inventory trankey, the record count is much smaller and performance improved dramatically. I hope that helps..

Sage 500

Sage 500+Scanco+Intermec-CN3

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING