Forum Discussion
Why is Access so slow?
I am using Access 2016 and we have multiple users, each with their own front end. Microsoft Access takes a crazy amount of time to load any form or report or query unless its within the first ten seconds of opening the program. I have tried compacting and repairing and really have no idea what to do to fix this. Any suggestions?
Ouch. That's my mistake. I forgot to ask about the size of the recordsets involved.
Loading that many records will be slow, IF you load them all at once into a form's recordsource.
I think that explains it in a way.
When you first open a form bound to one of those tables, Access loads only a small percentage of the whole, enough to show an initial display. Then it begins loading the rest in the background. If you ask for all of them, by moving to the "last" record in the recordsource, it has to retrieve hundreds of thousands of records to do that. Improperly, or inadequately, indexed tables are even more of a problem in that situation. And forms with lots of combo boxes or list boxes or subforms are also problematic in that regard.So, the solution would be to avoid ever loading all of the records in a table unless you have some specific reason, such as an aggregate query that needs to display the entire history of the transactions in a 430,000 record table.
Start with a single record. Offer a way to select a different record when needed and load only that record. And so on.This kind of problem also reflects the fact that an initial stage relational database application may contain only a small number of records, and hence the problem of mass loading is not obvious. Over time the number of records in the tables grows and the problem makes itself apparent.
Moving the data to SQL Server should help in allowing the Server to manage those huge recordsets, but I'm surprised that it completely disappears.
Again, my apologies for not thinking through all of the possibilities, including this one.
Lateralusx777 There are multiple possible reasons and it's hard to diagnose them from afar.
I recently had a similar problem on my two-computer peer-to-peer network. After days of trying different things, I finally discovered the problem was my backup software which was running continuously and consuming all available system resources most of the time. Not that your problem is likely to be that, but by way of explaining there can be different reasons in different environments.I would also investigate anti-virus software running on your computers. That can be a source of the problem in some situations.
Do you have persistent connections set up in all of your users' front ends? That is a potential problem when it's not done.
Where is the back end located? What is it, an ACE back end? SQL Server on premises? Hosted SQL Server or SQL Azure?
George_Hepworth What do you mean by persistent connections? It is Access back end on a file server on premises. I am currently in the process of converting it into SQL but that is going to take me a while so I was looking for a quicker fix. We have probably 10 users connected at all times.
Here's a link to a good overview discussion of performance, including the use of persistent connections.
