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.
Cheers,
Alec
Hi @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.
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.
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)
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.
Thanks,
Alec
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.