Checklinks taking too long (or its time to truncate history)
60GB database, works fine operationally except for the time checklinks and reconcile take to complete. They both take too long for the time available to run them. It is a pity that these can’t be ran over ranges or be more efficient/quick.
Sales history has 7.2 million records alone. SQL server is very capable when configured correctly at handling this size of database with good performance. However the check links process is so slow and makes it necessary to remove history in order to get it complete in a timely fashion. In this day and age is seems a pity to be forced to ditch the data when we have SANs and RAID arrays with multi terabyte disks and the hardware can otherwise handle it.
There are products that help with this situation, professional advantage have the leading solution, Professional Advantage Company Data Archivedatasheetthis is neat in that it gives vision of the archived records from GP client. Unfortunately our bespoke reporting would need rewriting to span the companies. This is also true if we were to copy the live company and then truncate history, this is a technique that is also a common practice.
Purchasing history has 7 million records. Lots to plough through.
The advice you see on forums claiming it is possible for Checklinks to run as users are using the system is UNTRUE. If users are using the system the following report will print and no check links will occur. As this is not possible it leaves evenings and weekends and crossing fingers that it has completed by the time the users come back to work on Monday morning. It IS however possible to run reconciles while users are using the system.