We are on Acumatica 2020 R2 Build 20.215.0025 with some customizations that have been published.
The Prepare Replenishment process used to take under a minute to complete. However, by simply deleting an old Snapshot the process now takes ~20 minutes to complete.
I can replicate this issue over and over again in a SANDBOX environment (by restoring to a SQL backup prior to removing the Snapshot), but I haven’t found a way to fix the process after the Snapshot has been removed.
Has anyone experienced this before? Any thoughts or suggestions would be much appreciated!
Best answer by aclawsonView original
If you haven’t already I would make sure to run the Optimize Database action after deleting the snapshot.
That goes through and verifies that any database rows that aren’t linked to a company(snapshots are stored as companies with negative ids) get deleted. Though I doubt that is your issue as even if the rows didn’t get deleted properly I would expect performance to stay the same not decrease.
Do you get any errors or trace messages when you are deleting that snapshot? Maybe try running the request profiler while the snapshot is deleting and see if any events/errors get logged there.
Thank you very much for the suggestions, I appreciate them.
I have ran the “Optimize Database” after removing the Snapshot, but it did not help.
I have not seen any errors or trace messages “pop up” when deleting the Snapshot, but I haven’t specifically checked the trace after deletion. I will do so now.
Also, I have not tried running the Request Profiler and I am unfamiliar with the process. I will read the help section in Acumatica to try to figure out how to utilize that process, good suggestion.
Try to update statistics on SQL server (especially on the InSiteStatus and InItemSite tables)
The deletion of big amount of data might make statisctics outdated and system use non-optimal indexes (though it does not well explain why query takes longer after dataset was reduced)
We have ran the Update Statistics within SQL Server a number of times, as well as rebuilt indexes. That hasn’t worked either… Thanks for the idea though.
@markusray17 - Following up from yesterday, no trace errors were generated during the deletion of the Snapshot. Bummer.
Good Afternoon All,
Finally got this resolved, big shout out to Acumatica Support! Here is the customization script they sent, in case anyone else has this issue:
Edit by community manager ***Please work with your support if you feel this script will help you***
We just ran the create index script in SQL, as we self host, but figured I would post the whole thing.
@aclawson thank you for the Acumatica Support shoutout! Unfortunately this script could have unintended consequences for some environments. Users need to work with their support for this script.