Slow Queries After Deletions

[panthers1788]

I have a setup where I have a database front-end with another database which is the backend. All forms and VBA are stored in the front-end and the data is stored in the backend (pretty standard).

The client requested that I take the backend and "split" it into 2 seperate backends, as there was a straightforward distinction between 2 different sets of data, where 1 set should go in one backend and the other in another backend.

I didn't think much about it and simply went in and deleted some data inside certain tables by selecting, right-click delete (Not SQL delete statments). In other instances I right-click and delete on entire tables. (NOT an sql statmenet).

Once I completed this I noticed something that I found to be interesting. After deleting a bunch of data (and different data) from each backend (As to begin with I just made a complete copy and paste of the database, and then deleted the necessary data in each.) After doing those deletions the 2 seperate backends were exactly the same size as the databse was before doing any deltions on the previous "Master Backend". (around 22MB). I didn't think anything more about this until after I realized many users were having issues and I started looking into it. What I noticed was that running queries in both backend databases were running very much slower than before the deletions. The problems the users were experiencing all stemmed from the speed.

I did some research about the speed issue and came across doing a Repair/Compact. I did that and it did indeed make the backend database files much smaller. I thought this may have fixed it, but unfortunately it didn't.

I did some thinking of why the deleting of records would slow the database down and the only thing I can come up with is somethign to do with indexing. I never changed any default settings when creating these tables so I don't know if indexing was turned on or off, or if that would even be an issue. My thoughts would be that when running the queries it is not finding data that used to exist which is causing it to choke up, just my thoughts.

Any suggestions? Ideas?

 

[JHB]

Which type of connection are you using for the two backend databases from the frontend database?
Do the frontend database get connected to both database at once or how is it done?

 

[Pat Hartman]

If you have queries that join tables from different BE's, the BE should be brought back together. When tables from different data sources are joined in a query, the db engine cannot work as efficiently when they are in multiple databases as when they are in one.

 

[panthers1788]

Sorry, I guess I should have clarified. I also created 2 different front-ends which connect to 1 and only 1 backend. Each individual front-end has no reason to connect to both backends, just the one. Again identical to how it was before I split the backends.

I appreciate the help, any more thoughts?

 

[Pat Hartman]

Compact and repair the front ends also. This will force Access to rebuild the queries and that might help.

 

[panthers1788]

Compact and repair the front ends also. This will force Access to rebuild the queries and that might help.

I did a compact and repair on both the front ends, and the backends and it is still running slow.....Any other ideas?

 

[Mihail]

Look at the dependency in the FEs. Maybe this will give you an idea about what happen.

 

[Pat Hartman]

Try connecting each of the FE's back to the original merged BE. Is that slow?

 

[JHB]

Did you rename the backend databases, and maybe moved them to another location? If so, then try to rename one to its original name, (and move it back).
I suppose the tables are linked, how did you link them, manually?
Do you've some code running in the frontend database, looking for a certain condition, and it not found handled by error handling that you'll never get a hint that something is wrong?
I don't know if it could help, but who knows, create a new backend database from scratch and import all the tables.

 

[panthers1788]

Still slow after doing all suggestions above.

 

[panthers1788]

I am going to post a new topic on creating a setup that the user will run the database front-end and back-end database locally which would somehow or at some point update a back-end on the server. This would fix the speed issue as well as fulfill a client requirement of being able to work offline. Any thoughts/ideas on that?

 

[panthers1788]

:banghead:

 

[Mihail]

One more suggestion for the actual issue (your client ask for 2 BEs).
Give it what he ask. TWO BEs. Identically. Change only the names (BE_1 and BE_2). Then ansure that your FEs will point ether to BE_1 either to BE_2.
You can change some colors in your FEs in order to distinguish between them ). I'm pretty sure that all people will be happy.

 

[Pat Hartman]

Still slow after doing all suggestions above

You never said if linking back to the original BE was slow or showed the original speed. If going back to the original BE is slow, then maybe you changed something in the FE to make that happen. You should also go back to the FE/BE before you made any changes to reconfirm its speed. Perhaps something happened to your network.