For hosted customers please raise a new case online and reference the title of this article.
Other causes of slow performance should be ruled out first. However, if Query Store analysis determines this issue only occurs during finance or payment cycles then follow the steps below.
Confirm that AUTO_UPDATE_STATISTICS_ASYNC is ON. If not, run the following against the database.
ALTER DATABASE [Mosprod] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
📌Note: In the example below the database is called Mosprod, change where necessary.
Run the following SQL against the database.
UPDATE STATISTICS payments WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS real_commitments WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS invoices WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
You can also rebuild all the indexes for the following tables:
Invoices.
Payments.
Real_commitments.
Variations.
and Delivered_Actuals_Applied tables.
This can be done manually by:
Going into object explorer for the server.
Expanding on the specific table for the specific instance.
And then right-click on the 'Indexes' folder for the table.
And choosing 'Rebuild All'."
📌Note: The instructions above may depend on the database application that you use locally which may differ in method to index.
Thereafter, a customer replied to us to say:
"I have tried again, and I can now reject all invoices. It takes about 10 secs to reject a batch of 500 invoices."
It is recommended that the above scripts be ran out of hours were found to be necessary.