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 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.
- I did put that code in on form open and close for a persistent connection.
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. 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 agree as I have been trying to find a solution for some time now. The SQL instance is internal as well.
- I'm glad you solved the problem. However, it's disappointing not to be able to address the Access side. Out of curiosity, where is the SQL Server instance located, internally or hosted?
- I actually just made a huge breakthrough and converted everything successfully into SQL and now everything loads instantly. I appreciate your assistance.
- I have tested the accdb on server and on pc with no difference. We are using ethernet connections all around. I can ping the server all day with no degradation or lost packets.
- The fact that it starts out fine and then slows down implies that something is happening in the environment. You've ruled out anything in the accdbs themselves, such as the lack of a persistent connection, backups and so on I can only suggest things based on the information available here in this discussion.
Do the individual accdbs reside on the computer of each user, or do they also reside in the network folder with the back end accdb? Move them to users' computers if they are being run from a shared location.
I can't imagine that you are on a WIFI network, but let's verify that as well. - Also sometimes it works for the same duration to compact and repair my front end.
- Even though it always works fine for the first ten seconds or so? We only have one accdb for the backend that contains all of our linked tables and then a different accdb for each person's front end.
I have a few reports converted to run off of my linked sql tables on a cloned front end. These load instantly always. - I missed the statement that this happens with any accdb in your environment. That pretty much puts it outside of Access and points to environmental problems. I.e. Anti-virus or backup software or other network processes or perhaps hardware problems like bad switches, routers or cables.
Getting your IT involved seems like the only way to track down the source of the problem. That sounds so much like either code running on a timer event or an audit log making a back up that I'm stuck trying to think otherwise. Also, in my personal environment, I had back up software running that would kick in from time to time with similar patterns.
The only ways I can think of to pursue it further is to provide a copy of the accdb to examine or for you to create a separate accdb with only a form and a couple of tables to test with no code in it.
- Yes if we immediately open a form or report there is no delay, however, after roughly ten seconds or so opening the same one again or a different one would yield a significant wait time.
This is with any accdb.
I do not have an audit function running.
No timers.
It is replicated on all computers. Let's go back to this first statement, and maybe expand on it a bit.
"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. "
So, if a user starts an existing relational database application and immediately opens any form or any report, there is no delay in opening it. However, if they then close that form or report and attempt to use a different form or report, things slow down dramatically? Does that pattern repeat itself consistently? Does it happen regardless of which form is first opened? Does it happen if the user closes and reopens the same form?
Does this happen with any accdb? Or with one in particular?
Do you have an audit function that is copying records to an audit log, or some similar updating process running in the background?
How about timer(s) in an open form?
Can you replicate this problem on every computer on your network?
Lateralusx777 As I said in my first post, it's really hard to diagnose performance problems when the most common factors have been tested and found not to be relevant. It could be bad hardware in your network somewhere, a switch or router that is bad or going bad. I've seen situations like running a CAT 5 cable over the top of a fluorescent light and having the cable compromise by proximity to the ballast in that light. Failing NICs in one or more computers can be a factor. The fact of the matter is that if you've checked and validated the kinds of things Access can be subject to, then the most likely source of a problem is in the environment. Access has to have a clean, reliable network connection because it passes so much data back and forth between front end accdbs and the back end accdb. Other applications are much less susceptible.
Get your IT support involved and don't let them dismiss the problem without thorough follow up.
- That didn't yield any results for me. I cant really find anything about this anywhere either.