Access Performance

[netsmurph]

I have a large access db, which is around 170Mb compacted. It is split into front end backend. The backend is on a server.

The performance has decreased drastically - if more than one user is accessing it. (ie a basic search takes around 15 seconds as opposed to 2 seconds).

I am trying to eliminate possible causes - server, network issues, etc.

Does this sound really unusual for MS access, or have other users experienced similar performance degradation with databases this size?

One network issue that is being investigated is the firewall that is between the server and the client (this is beause this project is a joint venture, so the shared area firewall has a higher level of security than standard).

The long-term solution is upgrading to SQL, which is underway, but really need to see if there is any way to get this access version stablised in the interim.

Many thanks if you can assist.

Andrew

 

[Ed2020]

Netsmurph,

This is a difficult one to answer without seeing the database.

170MB isn't that large for a MS Access system. I wouldn't assume that moving to SQL Server will necessarily improve performance until you know what the cause of the problem is.

A few questions which may help us give some more guidance....

What is your network bandwidth like (Access produces a *lot* of network traffic)?

How many tables in the database? How many records in each?

Are all searchable fields indexed (including those used in table joins)?

Are you using bound or unbound forms? Are you using DAO or ADO to access data?

Can any of the data be moved to a locally stored MBD file?

Have you considered using database replication?

Ed Metcalfe.

Please do not feed the trolls.....

 

[netsmurph]

Thanks for your reply Ed,

In answer to your questions:

The bandwidth is being looked into with the network guys - pretty sure the server has a 1Gb network card, although it is a very overutlised server that many users are connected to as a file server.

There are approx 20 tables, with the largest tables holding around 20,000 records

The forms are bound - would you recommend using ado to access and update data? Would this be the reason why when 2 more more users are accessing the db it grinds?

There are some things that could be stored locally, such as look up tables.

Have not really thought about replication, but will look into it.

Any other pointers based on the above answers would be much appreciated.

Thanks again

Andrew