Our bank reconciliation is so slow - system takes forever to calculate. What data do we need to purge to make this process faster? Do we need to clear history?
Hi NicoleM if clearing the bank rec history doesn't speed it up enough, the process we use to significantly speed up the Bank Rec for our clients is to create new/missing indexes and then rebuild them in Microsoft SQL. We've had cases where selecting the Reconciliation tab, going to the end of the transaction list, recalculating, has gone from 2-3 minutes to almost instant.
The best way to do this to open the Bank Reconciliation in Sage 300 and perform some activities that are slow - really stress it out.
Then log into SQL Management Studio and open the SQL Activity Monitor:
Under Recent Expensive Queries locate one or more queries relating to the Bank Rec activity you just performed, right-click on it and select Show Execution Plan:
Check the Execution plan for Missing Queries (usually in green writing), open the Missing Index Details, and Create the Missing Indexes by executing the auto-generated SQL statements. Example:
Here is a guide I found online: www.intelliware.com/.../
Now create an SQL Maintenance Plan to rebuild the indexes for your Sage 300 database. I always recommend Ola Hallegrens plan: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Log users our of your Sage 300 database and rebuild all the indexes to take maximum advantage of the new indexes. Let users back in and you should see an amazing difference. If you haven't done index maintenance before or been actively engaged in it, you can make incredible improvements to all areas of the system.
Good luck!
