Access database response times (1 Viewer)

[Aenathras]

I hope that someone can give me some suggestions on a problem them is preventing me rolling out a Access database to some of our offices.

I am trying to ascertain how much of the problem I am having is due to:

• The design of my database
• The hardware infrastructure for which I am designing

First the geography:
Location A is where the database is located on a fileserver
Location B is where I am developing the database (and using/testing it)
Location C is a end user location who also wish to use the database

Distance A-B is significantly shorter than distance A-C and I beleive that the bandwidth/linespeed between A-B is significantly faster than A-C. I am investigating to see if more bandwidth is available but doing so will be both costly and time consuming. In the meantime, I am looking for other ways to overcome this bottleneck.

Next, the location of the databases
Initially I had a single database (FE and BE) but the response times at location C where prohibitively slow. I next split the database into a FE (3 forms, no reports) and a BE (data only). The FE database is 840Kb and the BE database is 808Kb. I don't think the size of either database is prohibitive but you may wish to differ.

Finally, the time tests that I have done so far
Test 1: Opening the database and loading the first form (which includes a subform)
Test 2: Opening a second form from the first form
Test 3: Closing the second form
Test 4: Closing the first form (and the database)

Tests 3 and 4 are very quick when run from both location B and C.
Tests 1 and 2 take longer to run in location C as in location B. Test 1 takes 12 times as long and Test 2 takes 17 times as long.

What I am seeking is advice and suggestions on ways I can narrow down why running the database in location C takes so much longer and if there is anything more I can do.

So far I have:

1. Reduced the number of forms (and their complexity) to a bare minimum
2. Split the database FE and BE and put the FE on the users desktop so that it doesn't have to be opened from the server each time
3. Included code in the forms that checks where a user is and avoids unnecessary processing if they are at a more remote location
4. Checked for unncessary queries and other objects and deleted them from the FE and BE
5. Reduced the number of database files to one FE and one BE file - previously more databases were linked

When I was at location C recently and did other tests such as opening a large PDF file from the internet it took very little longer in location C than at location B. I don't understand why this should be when opening two (smaller) Access databases takes disproportionately longer.

I hope I have given enough information to outline the problem that I am trying to crack - please let me know if not. I welcome any suggestions into other areas that I should be considering or if there is anything that I have missed.

 

[Rabbie]

The FE database should be on each user's PC. It should never be stored at a central location. Only the BE should be kept remotely. how is location C connected to Location A. Is it a WAN or a LAN.

 

[Aenathras]

Response to questions

The FE in the current design is on the users own PC and the BE (data) is on the central server of a WAN. I have the same configuration on my PC (that is elsewhere on the WAN) but their reponse times are significantly slower than mine.

 

[gemma-the-husky]

when you use a dbs all the data has to be fetched from ther server to the user's desktop

so, with a wan you need to reduce the ampount of data as far as possible, so try to write queries that have as little data as possible - try to filter the recordsets to the minimum - dont bind forms to large datasets, and sont use graphic images if you can avoid it.

if you can use a terminal server this will solve most speed problems, but you may then have issues printing and saving files locally.

 

[Aenathras]

Sub-forms

Thank-you for your responses.

With further testing, one key item that slows down the application over the WAN appears to be not the volume of data (though I agree with your reasoning Gemma) but in the use of sub-forms.

I have compared the following versions of my database running at the remote location that has been having problems:

1. Form A includes sub form B which contains tabular data. The query that drives the tabular data retrieves data based on data fields in the outer form A so the list of data is shown dynamically. There is an edit button on sub-form B that calls form C where the data can be edited.
2. Form A has no sub-form, just the text fields that are used in the query. A button on Form A calls form B (now a separate form) which shows the tabular data. The edit button on form B calls form C as before.

The improvement in the response times has been better than I could have expected. The end-user described it as 'much much much much' quicker'!

What I don't understand and perhaps someone can explain is why the sub-form had such an overhead. Having 3 forms rather than 2 in the database didn't increase its size that much and the logic and processing is the same.
Has anyone else experienced this one?

Aenathras

 

[Rabbie]

It could depend on how the query for the subform has been written

 

[gemma-the-husky]

if your original form used normal access linking then it may be that the subform retrieved the whole dataset initially, in order to be able to refresh the data, as you moved between main form records

if you are now just fetching the matching data as you need it, you are clearly reducing substantially the amount of data moved over the network.

[edited

depending on the way the query is written the jet server sometimes cannot resolve the query and limit the number of rows returned - it has to return the whole dataset, and the front end can then resolve the row issues - which causes speed issues, obviously noticeable over a WAN

i forget where the detail is, but i am sure MS knowledge base has info on the best techniques to write these queries efficiently]