How to speed up/improve performance for international DB users

[mx10411]

Hello,
First time poster here so I hope this doesn’t sound too remedial. Here’s my situation…

I work for a large industrial company that has locations throughout the world. We have a DB that tracks product concepts and ideas and associated metrics for those ideas. The DB resides on a file server in North America (Raleigh, North Carolina to be exact). North American users have no trouble with the performance of the DB. It takes a moment to open (several seconds), but once it has opened there is virtually no lag time to add or edit records, run reports, view graphs, etc. However, users in Germany and the Netherlands encounter substantial lag time not only in opening, but also in updating and entering records, running reports, and viewing graphs. This is true even after they have waited for the DB to open.

The size of the DB is only around 2MB so I don’t think overall size is the issue.

There are probably no more than 3 or 4 users in the DB at the same time with most occasions being a single user so I don’t think we are having a multiple user issue.

The DB is self contained – no references to external data or splitting of any kind.

So my questions are:
1. Do you think the poor performance is a function of our network or of Access or the DB design?
2. If it is the network, is there anything that I can do in Access to help get around the hardware/network issues?

 

[MartijnR]

If you are in the Netherlands or Germany, what is your ping time to the server where the db resides?

Maybe you can use replication, but I'm not sure if that will work for your situation.

edit: I think Wisconsin is in the USA, maybe ask a user in Europe to do the pinging for you

 

[Robert88]

Hi Scott Braemer ,

How to improve the performance within the LAN.

Split the database, keep the data (back-end) on the server and the application (front-end) on the local client.

How to improve performance over WAN (or between your international sites).

With your application split (usually done when creating mde files for the front end to secure your code) the back-end since it can be kept in mdb format, can be replicated (replication cannot occur on mde files).

General

The benefit of replication is that it only copies what is required to copy, minimising bandwidth on your WAN between international sites.

Of course your international users will also have the same application at thier desktop (front-end) but with network connection to thier local server, making it very similar to your home site, so I suppose in development you only have to change the path to the local server. I suppose you will have a front-end for each site. The servers replicating the data take care of information swapping between sites over your WAN.

I work in a company with sites in Australia, Malaysia, Netherlands, Germany, Switzerland and Houston.

We originally created applications looking at servers in other countries over a WAN but it seems that the speed at which these applications are opened are governed by the line speed the site has to the internet (WAN) or if looking for data at a site with a slower connection, the slowest connection obviously governing the connection speed.

In the meantime our company has standardised the internet connections as it is only as good as the slowest connection, or at least a minimum as Head office might have greater needs than between global sites.

But strongly emphasis replication as it takes a lot of bandwidth off the internet connection (WAN) and all users globally can access thier data through only using LAN (assuming a Server resides at each location to replicate to).

If LAN only is not possible then the further away you get form your server over an internet connection (WAN), latency kicks in (check with a ping). Generally the further away the data the longer it will take (not 100% true). There is a lot of high speed connections between America and Europe but not betweeen Australia and America, so a lot more latency is experienced between Australia and America than America and Europe althought nearly the same distance.

Usually internationally the path a packet takes is the path of least resistance. Usually when I send a ping to Germany from Australia it goes via America. This can be checked using a trace routing program and will change dependant on the traffic on the Worldwide network, but I think this is too much detail.

Q2.Network Hardware Your LAN network infrastructure? Personally I noticed substantial difference between a 100Mbit switch with 100Mbit network card and then moving to a 1 Gbit switch with 1 Gbit Network cards in clients reduced time to transfer large amounts of data, which Microsoft Access also enjoyed.

I hope this helps the performance of your DB's .

Robert88