Sorry for the delay, work got in the way. Resolving all the issues to do with Act and Sage have been a long process but using techniques harvested from this and other forums we seem to have got rid of the appalling slowness that was making these programs practically unusable.
So basically, we have gone through a lot of the stuff mentioned above, ran diagnostics, cleaned and repaired Act, and think it was actually Sage causing most of the issue through the Act Sage Accounts Tab, where it shows a live view of the customers accounts info in Sage, in Act.
We ran the Sage diagnostics, cleaned and repaired its database, deleted a lot of old information, and it sped up considerably. Archiving the previous years were not an option at this time but will be run at end of year in a few months, so I might update this post then to advise everyone further at that point.
Also, we upgraded the switch and every network card in every PC to gigabit, and this has sped things up considerably as well. Ridiculous really tho isn't it, that a gigabit network infrastructure is required to run a pretty simple database on 5 pcs. Anyway, it is now 100 times better than it was, if not back to its original speed of a few years ago.
Thanks for all assistance,
Best regards http://www.sageforum.co.uk/
Listed in order of significance [ maybe ]
A - Database Maintenance - HAVE NOT DONE ALL OF THIS
For gradual slowing another candidate is simple volume of data, e.g. audit trail size, plus areas such as the SOP and Invoicing Modules. If there are years worth of old orders and invoices in SOP and Invoicing then archiving some of those off, taking a few extra backups, deleting, then ReIndex & Compress might help. Try to avoid using Sage
as a long term archive. The obvious area of data that can grow very large is the audit trail, but large numbers of completed orders in SOP, posted invoices in the Invoicing Module, etc. can also significantly slow down a system.
The report module is also overly complex. Before it even displays the list of reports, it has to filter out the .SLY reports that have already been converted, and crazy stuff like that. You can speed this part up by manually deleting converted .SLY files and unused .reports.
In many cases, reducing the number of live transactions on the system - clearing out old invoices and transactions and relying on the data archive makes significant differences to the speed. Remember to compress data after clearing the audit trail.
B - Anti Virus - HAVE DONE THIS ON ALL PCS AND SERVER, NOT A MASSIVE DIFFERENCE.
One of the files that is opened and closed the most is queue.dta which is used to control record locking by users names. For a reasonable sized invoice this can be opened and closed 20 or more times. So if your AV scanner is set to monitor all files it will intervene every time this file is opened and re-scan the queue.dta file for viruses !
On a network, the whole problem is compounded because the operating system on the master PC is having to open and close files for all the PCs on your network, control the file locking and process anything that you may have running in the foreground on that PC, all at the same time.
Therefore, ensure all Sage directories are excluded from being checked when files are opened. If you monitor what Line 50 is doing with a utility such as Sysinternal's Filemon it reveals that Line 50 is constantly opening and closing the same data files many times a second. This is particularly noticeable when you post a screen full of
batch entries or an invoice with several product lines.
(Depending on what Anti Virus you use, you may want to completely un-install it from add/remove programs, reboot your machine and then try it again) You may want to check the server antivirus also as that could also be checking the files before they leave the server. Basically set Antivirus software on Client PC and Server to ignore
Sage folders, but at least the *.dta files (sage files) and specific folder (ACCDATA) and if able exlude all the followiign - add *.dta, *.coa, *.srt and *.sly to your antivirus exception list
C - Network Tweaking - HAVE NOT DONE THIS [ but have installed Gigabit speed gear ]
Ensure that the duplex on the network card is set to Auto on the speed / duplex option. If already set to Auto change to 100mbps Full Duplex or 1000mbps Full Duplex (if you have that option) If you believe it could be the network causing the issue, take a backup of your database
and install it locally on a single PC and then test it.
NB - Duplex can be somewhat less effective than they are advising, as different cards use Duplex in different ways and the wrong settings can make things worse, not better.
D - UNC Path - HAVE DONE THIS [ standard install ]
Do not use \\your-server\my-data ALWAYS use from a mapped drive i.e T:\my-data, UNC paths and Sage don't work well!
E - DNS Setup on Router - HAVE DONE THIS, CHECKED IT IS NOT THE CASE
The router might make a difference if it was set to give out a DNS address. This might affect Windows "Name resolution" which is how Windows finds things on the network. If not set correctly it would be asking your ISP where your Sage data files are before trying to find
them locally which would really slow things down
F - Updates - HAVE DONE THIS AS FAR AS ABLE AS OLD VERSIONS OF SAGE NOT THAT WELL SUPPORTED
Ensure your Sage product is fully up to date with all the latest service packs and hotfixes http://www.sage.ie/Support/line50/Downloads.asp
Installed SP1 as standard and no other hotfixes are available for version 12.
G - Unverified Tweaks - might be worth a try
= Add this line into the Sage.ini file on every client PC:
Files=100 ( Put this in the section "[SG50]" )
= try switching xp sp2 windows firewall off
= What is the transaction number you are on? If it is above 25000, it may be the file-locking that is a problem...
= You should check that "oplocks" has 'not' been turned off on the server or any of the workstations. By default it is on, however some multi user applications turn it off when they are installed. More info on oplocks here :http://sageforum.co.uk/viewtopic.php?t=915
or Google "oplocks" or "Opportunistic Locking".
= Check the RAID array drivers. If you still have a drive in the server that is not part of the raid array you could try temporarily shifting the Sage data files on to it to see if it makes any difference to the problem. Can do this as the extra drives we put in the server are mirrored, not RAID 5, might be worth a try too if nothing else fixes this issue.
NB - You may find the following bit of software useful. It will show you all the file system requests that are being made when Line 50 is running. In advanced mode it also shows the FASTIO requests as well.
Freeware File Monitor from Sysinternals now Microsoft http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx
When you first start the monitor up you should disable monitoring of your C: drive from the Volumes menu and just leave "Network" ticked. If you click the little clock tool bar button it will toggle the output between timestamping and showing the duration of each file operation.