Performance degredation after splitting an Access DB

Question

We've got an Access Database Application. (Disclaimer: We'd have preferred a different technology, but things happen ...). In order to use and develop the database in parallel, we've split the DB into a "data" (backend) and "application" part as seems to be best practice for Access DBs.

Now the performance has been degraded, queries that used to be instantaneoulsy now take a couple of seconds. This was a bit surprising, since "better performance" was supposed to b one of the pros for splitting the DB. What can we do to get performance back to the original level or even better?

---

UPDATE

We've done some more testing to figure this out. It seems to be that the problems are caused by the workgroup file (.mdw) that is accessed permanently and causes quite some network traffic (with the unavoidable latency issues). When I copy the .mdw to a local file and use that instead, the performance is great. Obviously, copying the file is not a good solution (as we want to make the Access DB available on the network to an unknown number of users). Any better ideas?

Answer 1

The only good answer is to switch from using Access to using some other set of technologies.

Answer 2

You need to ensure that each user has a copy of the front-end. You also need to ensure that the path to the back-end is short. Tony Toews, MVP has more performance tips.

Answer 3

Something worth looking at is whether or not the production app is just updating linked table connect strings. Sometimes Access caches metadata in the linked tables that causes wildly inefficient querying of data. The solution is to completely delete the linked tables and create them from scratch in that case.

This would likely only matter, though, in cases where you had a different testbed server than the production server. If both are on the same server, this is much less likely, though it's possible for a long path to cause a performance bottleneck.

It's one of the suggestions in Tony's Performance FAQ but let me point out that where you put the back end makes a difference. I avoid long pathnames and try to get all my back-end MDBs stored in a top-level store on a server. That is, \Server\Databases instead of \Server\Data Files\My Workgroup\My long pathname with spaces and stuff.

Answer 4

Added some new info in the original question. Hopefully that'll help someone have an idea that helps solve this issue.