Specific Query not loading after bloat

[crazyhead247]

Hello there,

I'm running a Access Database used for tracking a lot of information within a reasonably small company. Just recently (after making updates which included changes to some forms and tables, as well as adding 2 new forms) I noticed that one of the queries was slowing down, proportional to the amount of bloat. After a quick compact and repair, the bloat disappears and the query runs almost instantly. Then throughout the day as the bloat grows, the query takes longer and longer to load.

Some numbers just for reference. Approximately 60 people using regularly, 1.22 GB after compact and repair (query in question takes 1-2 seconds to load), 1.31 GB after 24 hours (query takes 2-3 minutes to load). The query is fairly long (inner joining 5 tables, some with 700,000+ records), but it needs to be as all the information is relevant to the form. Query load time increases to 2 minutes 4-5 hours after Compact and Repair.

Oddly enough we're only seeing this slow down on one form. Nowhere else throughout the database. When I move the Access file off the server to my local machine, the load time is basically instant. As you could probably assume, the database is not split and is kept on the server, but splitting and keeping the FE on the users' local machines is an option we're looking into.

The database is also not compiled, as there are a lot of methods in the VBA code for objects that have since been removed (I inherited the database from someone else). Access will not allow compiling when there are references to object that don't exist. Could this be the cause?

Is there some way I could find out exactly where the bloat is coming from? There are a lot of users using it simultaneously and doing very different tasks, so it's hard to isolate the problem.

Another thought I had was to put proper error checking in every method (some places it resumes, while others it actually displays the error and then resumes) to see if some errors are popping up in other modules.

While we did have bloat prior to the recent changes, we have never had the slow down occur so quickly. Prior to the recent changes, a compact and repair was required ever 3 or 4 days, where now we need to do it twice a day.

[zmbd]

+ Bloat is a perianal issue with Access.
There are a lot reasons
Some of the more common:
1) Not explicitly releasing DAO or ADO record sets in code either because of poor coding practice or failure to have error handling that will close open record sets.
How to prevent database bloat after you use Data Access Objects (DAO) Link to Acc2003; however, still relevant to the newer versions from what I can tell.

2) The use of temporary tables.
If you have to have a temporary table what I have found to be the best way is to in VBA create temporary data file for the user session (various methods) and then use that as a container for the temporary table. Deleted as part of the session exit code.

3) Loops with Action Queries can occasionally contribute to bloat. I've not ran in to this very often; however, I have read in other forums and blogs that these can cause the Older JET-Database engine to re-compile and store the query contributing to the bloat (especially if these are working on temporary tables!). If you are using a lot of UPDATE, INSERT INTO type queries or if they are in a loop it may be better to use the RS.Add and/or RS.Edit methods. We just had a member here using a new version of Access that was also running in to large bloating so this may also be true for the ACE-Engine as well.

4) Storing attachments within the database.

5) The missing objects that are preventing your compiler from completing properly. I cannot stress enough the need to clean this up as soon as possible. At a minimum:
- Open the VBA editor, right click on the MENU bar, in the shortcut menu select "Edit" to show the editor toolbar. This toolbar should have a the "comment block" command - why this isn't available in the menu is a mystery.
- Now select the offending VBA script, and click on the "Comment Block" command... HURRAY... you should now be able to compile the project.
>> KEEP IN MIND>> there may be other reasons the code will not compile. You might consider reading Post#2 - Section A and the links therein if this step doesn't allow you to compile the database.

+ As you have not explained what changes you've made it's difficult to troubleshoot.

+ Will splitting the database help?
Maybe, maybe not; however, with multiple users it's advisable to do so.

+ Working faster on local PC vs networked...
This more than likely has to do with the quality of the network and/or if you have multiple users all using the same file at the same time (none of them have the database open in exclusive mode) over the network. If multiple users have the same file open it can really slow thing to a crawl in and of itself and may compound any issues you were/are seeing with the bloat - splitting the database may help.... it may just move the bloating to their version of the front end... just depends on the database design.

[jforbes]

ZMBD has provided a very comprehensive response, I just wanted to chime in on a few points:

If you are having the slow down on only one Form, we could take a closer look at the Form and its Data and see if there is something there that is causing your problem. Things to look at would be the Forms RecordSource, Filter, and possibly the code behind the Form. On the RecordSource, if there is any Filtering or a Where Clause, make sure there are the appropriate Indexes.


You'll definitely want to get your Database to compile. Even if you don't plan on creating a .MDE or .ACCDE Frontend, you'll want your Database to compile. Who knows what kind of other problems you are experiencing with an un-compiled Database. Also, Access will not create the .MDE or .ACCDE on a Database that wont compile.


Last point is that you have probably outgrown Access. 60 users on a ~1.2 Gig database is still within usable limits, but with that usage, the application would most likely be key to your organization, and would be pretty disruptive if the Database was lost. There is a lot to consider when upsizing to SQL Server, but you are probably at the point where you should consider it.
A few of the Benefits would be:

• the Bloat problem your a concerning yourself with would go away.
• Your data would be more secure.
• Your Queries would be faster.

Some Negatives could be:

• Additional cost of hardware and software.
• You would need more computer expertise in-house to support the Database as SQL is more complex than Access. (people tend to overlook this)
• The time needed to perform the Upsize, as it's not a small project.

[crazyhead247]

Hi Guys,

Thank you very much for the comprehensive answers. They're very helpful in providing general information about maintaining Access Databases, as well as specifics about this current one. I am currently in the process in closing (and setting to Nothing) each Recordset object opened throughout the entire database (Suggestion #1) as well as deleting code that references deleted objects (Suggestion #5). After that, I'll be compiling the database and hopefully we'll see the problem fixed. We don't use temporary tables, action queries, or store attachments so I think those recommendations do not necessarily apply to this specific database.

As a followup, I've discovered a rather interesting temporary solution, that I'm hoping will provide more insight on the problem. I've noticed that when the users using the form in question (only 4-5 people use it regularly) do not close Access or the form after a rebuild (when it's running quickly), it continues to run quickly and load the query almost instantly. Previously, the users would close out of everything during lunch and at the end of the day, and only after returning and opening access and the form they see the massive increase in load time. As I temporary fix, I have instructed them to not close Access or the form at all. I could not make heads or tails of this and I'm hoping that maybe someone could help use this information to better isolate the problem.

[zmbd]

1) Are there events for Unload, Deactivate, Close events?
2) I'm still not clear, are all of the users in the same FrontEnd database at the same time? To be clear, there is one database file and all users are using that database file.

If so, then what may be happening, and I am only guessing here, is on close the engine is saving each user's compiled queries (all of them so one user may have a dozen or more compiled queries depending on the design) to the system tables. For performance, Access was storing these compiled queries in the user's PC memory space. Once flushed to the system table, Access is then wadding thru all of these stored versions from the optimizer for each user so there's a very heavy load against the tables... (so 10 users, say 6 optimized queries, flushed become 60 in the system and all ten then try to run against these 60 so 600 read attempts against the stored plan, where before the user only was reading against the 6 in their PC's memory) Then each time the user opens that form more compiled queries are created, stored on close, rinse, repeat, drown in the drain with the stored query plan. Now I'm basing that guess on the link to MS's site in Post#2 and a few articles I've recently read that discuss Access-Bloating; thus, I may be completely off-base.

[crazyhead247]

There are no Unload, Deactivate, or Close Events. All users are accessing the same database file which is NOT split into FE and BE. The compilation and closing the recordsets may have done the job, but I will update in a few days as we see how performance goes.

[zmbd]

fingers-crossed.

I do recommend splitting the database with multiple users. :)

-z