Split database performance

[Gledders]

About 3 months ago I split our company's main access 2010 database as follows:

- gpm_BE.accdb - on server (windows 8 PC not used by anyone, it acts as a file server) - backend database is about 50Mb in size containing just tables.
- gpm_FE.accdb - on user PCs mixture of Windows XP, Windows 7 and Windows 8 (don't ask!!) - maximum concurrent front end users 7. The front end has no tables in it, just forms, queries, reports, macros and a few modules. All data is accessed via linked tables to the backend database.

At first performance was not great - due to our network being running on an ancient 3com 10meg hub. Changed this to a new 100meg switch (wiring won't take Gigabit) and performance for ethernet connected users was great.

A couple of months have passed and I started to notice that initial login to the database - the frontend has a rudimentary login form - was slow it would perhaps take 30-60 seconds to load up the initial form and connect to the employees table (which has only 9 rows in it). Investigation (i.e. lots of Googling) has led me to believe that this delay is probably due to the creation of the lock file on the server (gpm_BE.laccdb). I am not overly concerned about this delay but ...

Over the past couple of weeks two users who share a laptop which is wirelessly connected to our LAN have complained about the time it takes forms to load up. Not just initially but during the day. The front end has a number of menu forms which are used to group together forms into functions - e.g. product data management, invoicing, order management, stock control, etc. The problem that they are having is that they will normally leave the computer on a menu form (i.e. with no (linked) tables open). Sometime later when they return to, say, check on a manufacturing jobcard, it will take 30-60 seconds or more to load up the appropriate form and display the data.

My investigations via Google led me to a potential solution to "Always Keep a Connection Open to the Back End Database While Your Application Runs" - which I found on the FMS website.

However, when I tried suggested code, it caused problems using the database, for example, forms would not be able to access data due to "another user having the database locked" (or something like that). So, I quickly pushed that 'solution' to one side.

I found this another page which suggested the following:

The simple situation would be to create a simple form based on a table. Bound form means that the record source of the form has a table or query specified. Any table but one with as few records as possible. Or create a dummy table and put one record in it. In your startup form add the following line of code in the startup forms OnOpen event.

DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden

I've tried this latter option and it certainly doesn't cause a problem but I am not sure yet it if it will solve the performance problem.

I'd welcome any comments or feedback that anyone may have.

Thanks, Simon

 

[gemma-the-husky]

almost certainly the problem is a "persistent connection"

you need to connect to the backend when the dbs starts up, and maintain that connection throughout, to prevent repeated negotiation of the link, as you have learned

I use a connection to a recordset. others open a hidden form.

I tend to use a Dummy table for that purpose, that has no other use within the database.

note that you do not (should not) need to reconnect the tables each time, which will also help.

if you have to reconnect, you will note a difference if you connect one table - establish a connection to it, then connect the others.

 

[Gledders]

note that you do not (should not) need to reconnect the tables each time, which will also help.

if you have to reconnect, you will note a difference if you connect one table - establish a connection to it, then connect the others.

Many thanks Dave - what do you mean by "reconnect"?

I've gone for the hidden form option as it seems easiest to be honest.

Thanks again, Simon

 

[gemma-the-husky]

when you start up, do you assume the tables are already there - or do you link to the tables each time. the way you said it took 30 seconds each time, made me think you were re-linking each time.

you shouldn't need to have to relink, or reconnect the tables in normal use. you would only need to do this if the link fails for some reason.

 

[Gledders]

Ah I see. No the links are set-up in the front end database as a fixture. The only time that they get changed is when I do a new release of the front end database AND there are some back end database changes at the same time. Such as a new table, or new columns in a table etc. Otherwise, the links are persistent.

We've never had a failure of the links. As far as I can tell the performance issue is due to establishing a connection to the back end database which:

a) at initial load of the front-end is not unacceptable; but
b) during use (even after being away from the keyboard for 30+ minutes) is less acceptable, and is certainly irritating for the two users affected by it. Not least of all, because the laptop they use is situated in our bosses office and he wonders what the heck they are doing several times a day sitting there apparently doing nothing. When in fact they are waiting for the computer to spring into action. Not that we work in a draconian environment you understand ;-) - because we don't! Haha ...

Many thanks again, Simon

 

[Pat Hartman]

Connecting to an Access BE using a wireless connection is dangerous. Either get rid of the wireless connection or switch to SQL Server.

Even the best of wired networks have occasional blips but wireless networks have them more frequently. Access is like the canary in the coal mine when it comes to network issues. Access can't reconnect to the BE if the connection is disrupted and so it gives errors and sometimes even corrupts the database.

Since Access is disconnected from SQL Server anyway, network issues don't usually cause problems. Access sends a query. Access receives a resultset. Either could be disrupted but the network error correction usually recovers. The connection to Jet/ACE is more permanent, especially if you are attempting to maintain a continuous connection via a hidden open form or recordset.

 

[Gledders]

How easy is it to change the BE to SQL server?

 

[Pat Hartman]

Depends on how your forms are constructed. Technically, you can use the upsizing wizard and it will just work. In reality, most people find that the cold turkey switch actually makes things slower rather than faster.

To optimize the app for client/server (do a web search since there are lots of interesting articles and suggestions), at a minimum, your forms must be bound to queries that include selection criteria. For most forms that have only one or two search options, I add combos or textboxes to the form header. Then I modify the RecordSource query to reference those controls.

Where CustID = Forms!frmCustomer!cboFindCustID;

If you are using DAO or ADO, you'll need to change an OpenRecordSource methods to include the dbSeeChanges argument.

Once you get past those two, you will need to examine your queries and make sure they don't include anything that will prevent Access from passing them through to the server. Access makes every effort to "pass-through" all queries but if it can't, it will request that the server send down entire tables so that Access can do the processing locally. You of course want to avoid this at all costs.

 

[Gledders]

Sounds like getting the wireless network out of the equation might the best thing to do in the short term. Realistically, I can't see my boss wanting me to migrate the BE to SQL server anytime soon.

All this said, we have not experienced any issues other than this performance issue with the laptop.

Would another solution be for the laptop user to have their front end on the server as well, thereby having no network connection between the FE and the BE?

Thanks for all the advice! It is really interesting and helpful.

Simon

 

[David R]

The network load of loading the FE from the server to the laptop's screen will be worse than trying to move just data, by an order of magnitude at least. I would definitely explore a wired solution if I were you.

 

[Gledders]

Yes David, thank you, I will do that as well to see if we can minimise wireless activity.

 

[Pat Hartman]

I'm not sure why the laptop users are using a wireless connection in the office. Aren't there ports they can plug into?

 

[Gledders]

No Pat, we are a very small company and our wired network has no free ports on the switch! I might have to get my boss to pay for that to change!

 

[Pat Hartman]

You could remind him that a new switch will be cheaper in the long run than recovering a corrupted database. A wired connection is also faster unless you have a really bad LAN.

 

[Gledders]

That's a very good point, which I will use!