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.
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.
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.
- 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.