Problem reported
Goods receiving, purchasing and accounts have all reported certain GP forms taking a long time to open. If you logged in as admin or another user then the problem did not occur, if the user profile was deleted and recreated the problem went away.
Above: AutoComplete is a great feature that I welcomed but now is causing problems
Found the solution
The clue to the solution was this post:
How to troubleshoot slow performance in Microsoft Dynamics GP [login required]
Performance issues that occur when you open windows
The AutoComplete feature may cause performance issues when you open windows in Microsoft Dynamics GP. To turn off the AutoComplete feature, follow these steps:
1.Open the user preferences. 2.Click AutoComplete.
3.Click to clear the Show AutoComplete Suggestions check box, and then click OK. 4. In Windows Explorer, delete the AutoCmpl.dat file and the AutoCmpl.idx file. These files are in the following folders:
•In Microsoft Dynamics GP 10.0, the files are in the following location:
Document and Settings\username\Application Data\Microsoft Business Solutions\Microsoft Dynamics GP\dbname\
•In Microsoft Dynamics GP 9.0 and in earlier versions, the files are in the following location:
Document and Settings\username\Application Data\Microsoft Business Solutions\Great Plains\dbname\ 5.Repeat step 1 through step 5 for each user.
The part that caught my eye was the use of “Document and Settings\username\Application Data\Microsoft Business Solutions\Great Plains\dbname\”, fitting nicely into the pattern we were seeing with other users not experiencing the problem on the same machine.
Thus I guess that the auto complete file is loaded into memory when one of these forms is opened. Even if the user settings don’t say to use auto complete I think it must be loading it anyway and just not using the data. Thus just switching off auto complete, that we had tried in the past would have no effect if a pre-existing auto complete database exists for that user database machine combination.
Applying the solution
A quick test and indeed the above measures did sort out the issue for the few users we tried it on, however we have about one hundred users of GP so we could not really spend all day clearing out the auto completes. Instead I did a Google to see if we could globally reduce the number and period for which the suggestions are kept. It seems this is possible see;
Enabling AutoComplete for all GP users - The Dynamics GP Blogster
I didn’t want to globally switch off this wonderful feature, merely tame its data repository. Thankfully, reading this post you can see that the user settings table holds the value for the number of suggestions as well as the period to hold them.
Thus I wrote a quick SQL script to run from a SQL job overnight to tame everyone’s settings. The script does not change any preference to have the auto complete feature on or off, but instead tames the number of entries held in the file. Some of our users’ auto complete files were over four megabytes hopefully enforcing a policy will reduce that file size down.
-- Apply autocomplete policy to users
UPDATE DYNAMICS..SY01402
SET SYUSERDFSTR=
CASE WHEN SYUSERDFSTR like 'TRUE%'
THEN 'TRUE-3-50' ELSE 'FALSE-3-50' END
WHERE syDefaultType=30
It will now be interesting to see as each field is visited if the file will trim down from its original settings. That will mean a script to clean these files for all users will not be necessary, as the files will self regulate themselves down in size.