Update queries sometimes very slow

[GregL65R]

I've been tasked with updating an Access 2007 database. I have a lot of experience with Excel VBA but not much with Access or any other database.



I was asked to add 3 fields. That involved adding them to several tables, queries, and sql statements in the VBA code.

When I was done, it worked, but sometimes some of the update queries are very slow when run over the network -- a few minutes instead of a few seconds.

I haven't been able to get it to run slow from my local machine, but that isn't an option for production. For production it needs to run over the network.

It isn't always slow over the network, sometimes it's fast. However, the backup from before I started always runs fast.

The affected update queries are not ones that I changed. Also they don't reference fields that I added, even indirectly such as with select *. They explicitly reference fields that were already there when I started.

I tried Compact and Repair on both the code database and on the data database. I was surprised that both ran very quickly, only about a second or so. The code db is getting close to a gigabyte, and the data db is about 33 MB. Is it possible the compact and repair didn't really run? How can I check, and what can I do if it didn't really do a compact and repair?

At the time I started, the code database was about half a gigabyte. Now it's 900MB and seems to be growing fast. I can't imaging that my changes added all that size. It has some temporary tables, but they get cleared out each run. Is it possible this database has become corrupt?


Thanks,

Greg

[Dal Jeanis]

1) When you say "Code database", do you mean the front end?

2) Create a blank test database, then import all the objects from the existing "code database". Run that test database against a junk version of the "data database", and see if the test database balloons as well. If not, then you've got a corrupt "code database". That solves your balloon problem.

3) If the size changed, then C&R ran. If not, then it may not have. Usually that isn't the problem, though. You may have a problem with the database or some portions of it becoming uncompiled or "raggedly compiled".

To test this, you can try to DECOMPILE a junk version of the database, save it, reboot your system, then open it and compile it and test as above.

4) Are the slow queries being called from VBA, or macros, or what? The reason I ask is because any change to the VBA that sets up a query call might inadvertently affect the query.

5) There are a large number of things that can affect performance over a LAN. If none of the above works out, then we can try other stuff.

[GregL65R]

Problem solved. Apparently the "compact and repair" isn't complete until closing and reopening the database?? After doing that, it's now down to about 150MB, or less than a third the size it was when I started. I just did three fast runs in a row over the network, all faster than it used to be before I started. The data database is smaller too, down to about 22MB.

[Dal Jeanis]

Yes, the C&R actually doesn't happen until you close the db. I have a button set up to do all that.

If you encounter ballooning again, try the options in 2 and 3.

Please mark Thread Solved. Top of page, under thread tools.