This is a long explanation, please bear with me.
Overview: I have been working on a split database since 1998. It is a A2003 version and I am now using A2007. It has a front end DB that connects to 4 back end DBs. I have it setup such that whenever I use/edit the front end, it opens a hidden form to lock records to the backend databases. The front end database is distributed (via script) on boot up on each user's workstation. There are on the average 8-10 users connected at a time to the back end DB with a maximum of 16 users. The front end DB is huge it now has 104 linked tables, 303 queries, 256 forms, 106 reports, and several modules.
Problem: I have noticed over the last 1-2 years that the performance has been degrading while I am in the database programming updates or changes to the Front End DB. It is now to the point where it is extremely slow to make changes to the database. For example, when I am in a form that has a subform, and I try to move a field in the subform, it takes 20-30 seconds to select the subform's field and move it. Furthermore, it takes 30-45 seconds to save some of the forms. The front end DB that I work on is not the same version that the users are working on, but it is located in a BETA folder until I publish it to be copied the next morning to the workstations.
-The performance of the overall database in general use is not affected by this. IT IS ONLY WHEN I AM EDITING/PROGRAMING.
- It seems to be much slower only during the day when users are connected to the BACK END databases. Then everybody is gone for the day, it is much quicker and tolerable.
- It seemed to get slower when I decided to "clean out" (delete) older unused forms / queries and reports.
- I do have user security and permissions enabled and have had several users added and removed over the years.
Here is what I have done so far to fix this:
- Compact & Repair (I do this everyday before I deploy the new version of the Front End)
- Decompiled and Compiled
- Encode and Decoded
- Used the analyze performance tools (in A2003) did not really find anything
I need new suggestions. This is getting crazy slow.