Forum Discussion
Why is Access so slow?
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.
Going back over everything in my head again, I come back to two things. One we previously discussed, one I hadn't brought up. So, record-locking and indexing can be sources of conflict and slow performance. I would expect indexing to impact things from the start, though. Record-locking would impact things in a heavy transactional operation. Again, why not from the start, though.
The other thing I want to bring up again is the establishment of a persistent connection between each Access accdb Front End and the Back End. I think you did implement that, correct?
Typically, what I would have done is create a small form bound to a small table, i.e. one with only a few fields and records. Open that form hidden when the FE opens and leave it open, but hidden, until the FE closes. That forces Access to maintain the connection continuously.
Other wise, I am still looking around for things like back up software running whenever a change is made to an accdb FE, or maybe Anti-virus software.
I did not have an issue with a smaller data set. For reference though our main table in the database has some 430,000 lines each with 16 fields.
As far as the anti virus or backup goes it still comes down to why not in the first few seconds.
- I can compact and repair the front end while loaded and it does the same thing. It allows about ten seconds of speedy use and then back to normal. Yes it is most important that I found a solution though.
- We'll have to disagree on the most likely reason. I doubt it is the accdb itself. However, the fact that you've found a solution is the more important factor.
- The fact that it completely disappears leads me to believe that something with the accdb on backend was causing the issue. Again I appreciate the help.
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.