Dynamics GP slow to login or open forms, other users ok

Original post:14th March 2009

Updated 2016:

I notice that Ian had a similar problem that this solved too, see his post here: http://www.azurecurve.co.uk/2015/03/slow-opening-windows-in-microsoft-dynamics-gp/

Updated 2015:

GP offers a cache clear button (Remove Entries), under user preferences, I don’t think it existed/didn’t work or I didn’t know about it when this post was written, hence the drastic measures of removing the cache files. It would be wise to check the size of the cache files before and after “Removing Entries” to see if the size really does decrease. I would still advocate some audit/enforcing scheduled SQL script as mentioned later in the post to protect users from setting values for auto complete too high, especially in installations with very large numbers of items or customers/suppliers etc.

AutoComplete Setup is found from the user preference window, by clicking on AutoComplete button.

The problem report as reported:

Goods receiving, purchasing and accounts users all report some GP forms taking a very long time to open. Log in as admin or some alternative user and the problem does not occur. It was also observed that when a user’s network profile was deleted and recreated the problem went away (discovered by accident due to having to do this for other reasons and finding it solved this problem).

For information the company has over seventy four thousand items setup in inventory.

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]
[more]

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.

Note the location of the file, “Document and Settings\username\Application Data\Microsoft Business Solutions\Great Plains\dbname\”,
This file is local to the user, fitting nicely into the pattern that other users are not experiencing the problem on the same machine and that clearing roaming profiles helped.

Thus it looks as if the auto complete file is loaded into memory when one of these forms is opened. **Even if the user settings has autocomplete off,**it must be loaded anyway and just not consume the data. Thus just switching off auto complete on this version of GP produces no improvement as the auto complete cache files already exist and are huge for that user & database & machine combination.

Applying a SQL solution

Although the above actions of removing the files addressed the issue on those users tackled, with over one hundred users of GP, spending all day clearing out the auto completes is not a fun prospect. Another fear is that as new users join the organisation they would have to learn the hard way about this issue, whilst gradually suffering with depreciating performance until the issues eventually gets bad enough to report. A more proactive solution is required.

Google turned up the following post that led to the GP settings table and setting values that control the number of auto-suggest entries held in cache and the retention period. Enabling AutoComplete for all GP users - The Dynamics GP Blogster The settings table is keyed on a setting feature and user.

Taming the feature and its data repository was possible as user settings table holds the value for the number of suggestions as well as the period to hold them, updating these values would be the key. This is actually done through the user settings form on a user by user basis, but the need was mass update, thus SQL is the solution.

So arose a SQL script ran from a SQL job nightly that will constrain user settings. The production script is written in a more complex to allow users to change values in the user preferences and only interfere if outside acceptable bounds. The script does not override preferences for feature on or off. Some users’ auto complete files were over four megabytes in size, hopefully enforcing a policy with lower bounds will make life better for those users.

    -- 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 be interesting to see as each field is visited by the individual users in the user interface, whilst using GP, if the files will trim down from the bloated sizes. This will mean a file handling script to clean these files for all users will not be necessary, as the files will self regulate themselves down in size. (Now it is 2015. this is now handled by the button remove entries that clears the cache in user preferences, see note at start of blog post)