Split DB Performance Over Network (1 Viewer)

Hi everybody,

I've created fairly complex database at work here in the US to manage and track a large amount of projects, reports, and data. Initially this was only used at our corporate office, but a need has arisen for users to access this database remotely from Europe. In total, only 15 people use this database spread across the US and EU.

Initially, performance was great here in the states, but our European users saw very slow performance. I split the database and store the backend on the network now and have users copy the front end to their desktops. This increased performance significantly for a short time.

Unfortunately, performance for our European users is slowly degrading back to a state they consider unusable. I'm looking for ideas to improve the speed.

My first questions to users with slow performance entirely focused on finding what versions of Windows, Office, and Outlook they are running. Everybody shares the same versions, so I'm not sure this is the root cause.

I found this link describing how to help improve split db performance. I wanted to ping you guys here first because you've always been incredibly helpful for me.

Any glaringly obvious things you would check before implementing significant design changes? Just looking for ideas for where to start troubleshooting to find the cause.

Access in general does not play well over WAN networks. There are lots of technical reasons, but the main cause is it's need to persist a stable local connection to the backend data.

A thread here https://access-programmers.co.uk/forums/showthread.php?t=249556 describes some work arounds including remote desktop access / Citrix environments.

The other possible option is to switch to a SQL backend which is more robust, and look at designing you front end to minimise data traffic, which can and will make a difference.

here is a link for things you can do to improve performance - unless already done of course!

https://www.access-programmers.co.uk/forums/showthread.php?t=291269

In general, the larger the geographic scope of your network, the worse it will perform. Not because of literal geography, but because when you are using Ethernet-based networks, there is a distance limit for an Ethernet segment. When you reach that limit, you have to pick something else for segment-to-segment operations, which adds a HOP - where your protocol packets must jump a gap between segments. This is a store-and-forward action, which means you add a delay for the store and forward steps.

OK. you probably knew that - but people don't always apply what they knew to their problems. So... the practical side of this is that as networks span longer distances, they have more hops and have to traverse connections that can handle the distance - which often means SLOWER transmission rates. (I did say OFTEN and not ALWAYS!) To keep connections robust over distance, you sacrifice speed. And something with global scope will have a LOT of distance to cover and lot of sacrifices to make.

The biggest problem with Access is that it uses SMB protocols for "native" Access data files. This is because the app runs on the clients and uses SMB (essentially, Windows File Sharing) to get/put table data. EVERYTHING goes over the network.

Your best solution might involve upgrading the back-end to an active engine such as SQL Server or MySQL or SOMETHING that can perform database processing locally with respect to the tables, and then use ODBC or SQLnet protocols to send queries to the SQL engine and return selective results back to the client. This will be more stable in the long run.