Adjust Cost Item Entries batch job (795) is taking more than weekend hours (38 hours) to execute and we cannot run this batch during working days due to table lock error?:
Server Configuration
Windows Server 2008 R2 Standard, Service Pack-1
Processor:- Intel(R) Xeon(R) CPU X5650 @ 2.67 GHz 2.67 GHz
Installed Memory(RAM) :- 24 GB
System Type:- 64-bit Operating System
…
In both in inventory setup and accounting period, Average Cost Calc. Type was Item previously. We changed to Item & Location & Variant.
Additional information
Average Cost Period: Day
Automatic Cost Posting: Yes
Automatic Cost Adjustment: Never
Data of almost Three fiscal years (07/15/2012 to 10/04/2015)
-
Opening data as on 07/15/2012
-
First: 07/16/12 to 07/15/13
-
Second: 07/16/13 to 07/16/14
-
Third: 07/17/14 to 07/16/15
Fiscal Year & Inventory Period Closed till 07/16/2014
………………………………………………………………………………………………………………………………………………………………
Now system has to adjust cost for total of
183,922 item records
2,173,866 Avg. Cost Adjmt. Entry Point records
8,867,151 value entry records
………………………………………………………………………………………………………………………………………………………………
Out of above total, we selected inventory posting group “LUBE” which comprises
359 item records
181,530 Avg. Cost Adjmt. Entry Point records
1,259,046 value entry records
We started running the report on 10-apr-2015 1 PM and it was running till 11-apr-2015 4 PM and suddenly it got rolled back. So, it run for around 27 hours
It almost took our week end but did not complete.
However when we did try for 10 items, it completed in approximately 5 hours.
Due to “table lock” error, we are able to run this batch during working days (Sun-Friday). That means we can run only from Friday 6 P.M. to Sunday 8 A.M. (approximately 38 hours).
………………………………………………………………………………………………………………………………………………………………
All of the above, this batch job is taking long time to execute. Besides following workarounds
(1) Running adjust cost item entries for item in small buckets (let’s say 100 instead of 359 at a time)
(2) Running Adjust-Cost Item Entries first (without cost post to GL) and secondly running Post Inventory Cost to G/L (1002).
Is there any other ways to increase performance of these batch jobs?