Sharp decrease in database performance (1 Viewer)

Greetings -

I have an Access 2016 multi-user application with the backend stored on a shared location. Each user has their own front end.

The front-end application covers many areas with the most active being treatment records of 4 types. The application is a navigation form, with different components located in different tabs. The most active area is where treatments records of 4 types are entered in one tab.

For a couple of months, there has been a sharp decrease in the performance of the database with some actions taking literally 1 - 2 minutes to complete. The impact is seen in pulling records up from a combo box in the treatment records form, but also changing from one record type to another (in a tab control), it really can take it's time.

In doing some analysis, it seems the maximum users in the database at one time is 16 with mostly it being anywhere from a couple to 10 or so. This has been consistent since before the difficulties.

There has been a sharp increase of treatment records in the past 2 months from an about 75 - 150 new records daily to now over 400 records daily.

While this is significant, I wouldn't think it would do that much to the performance.

I've also been told that this slowness can occur even when there are only a couple of users in the database.

The front end application has a hidden dummy form linked to a dummy table to assure consistent connection to the back end. I have been informed there has been no changes to the server or our connection to it where the backend is housed.

I know this is a broad question, but would anyone know where to start to see what the problem is? The level of use doesn't seem that much for such poor performance.

Any thoughts, at least on where to start, would be appreciated.

Do your forms bring all records in by default and then filter them? Do you have good network speed Gigabit on the whole network? Do you have sensible indexes on your main records, and primary keys?

Lots to look at to be honest.

If the back-end is Access (as opposed to, say, SQL Server or ORACLE), then you need to sometimes do a compact & repair on that back end. Not necessarily a lot, but it always helps to clean up. (Make a good backup before you do this, just in case something goes south on you.)

Look for anomalies in the folder where the BE is stored because that will also be where the .LDB file is stored. Things like multiple LDB files for the same name as the FE but with a date or an odd-looking number suffix would indicate permission issues, for example.

Issues can sometimes be tracked reasonably using Performance Monitor. I would look for CPU usage, network usage, and lock management as three places to start. However, since you say this started recently, the next question is, so... what changed? Did some table get a lot bigger? Was a new table added that wasn't there before? Did you start using some complex query that you had not used before? What happened?

There are all sorts of algorithms relating to efficiency of a database, so diagnosing from a distance will be tricky. However, computers are deterministic. (Despite appearances, they really do NOT have minds of their own...). So if behavior changed, that change had a distinct and concrete cause.

Fixing this might be easy, but as that sage provider of wisdom, Elmer Fudd, once said: In order to make Hassenpfeffer, first you must catch the wabbit. So if we are to fix your problem, we must first catch its cause.

I agree with the other responders --lots to look at
Here's a link to some performance tips from FMS.