Question database running too slowly over network

[antonyx]

Hello all, I have a problem with my database which I foresaw 2 years ago..

Here is my situation, I have 3 terminals in my office:

MAIN (vista ultimate)
SECONDARY1 (xp professional)
SECONDARY2 (xp professional)


I have a microsoft access 2003 database which has been split using the traditional 'front/back end' method.

My terminals are linked over a standard broadband/router network. None of the terminals are servers, they are all single users within the network (basic network all connecting to the router via Ethernet).

My back-end file is held in the MAIN shared folder. the front end file is present on all three of the terminal's desktops.


the database is nearly 3 years old and has many thousands of records (20,000 plus when taking into account all the tables). the database is not normalized as well as it should be. there is a lot of redundant data. i knew this would be an issue when i was designing it but we didn't have the time to refine it any further.

the problem i am having is that the database is running TOO slowly on the secondary terminals (And quite slow on the main terminal). i realize a portion of this slowdown is due to the overall strain on the network.


i need to speed up the database on the secondary machines. how can i do this? on the secondary machines i only need the most recent records (ie 2009). the main table is my bookings table which has the bulk of information. I have tried making queries to only retrieve >01/01/2009 but this is still very slow. can someone suggest a few steps to perhaps archive the old data? or use a more efficient indexing system for retrieval?

i await your comments. i shall be researching this further today online but any replies are welcome

 

[Rabbie]

I see you have split your DB with a FE on each computer. Have you run "Compact and Repair" on the BE recently. An unnormalised design can also raise performance issues as the DB gets bigger. Ideally you need to look at the design and see where you can normalise adn see if that helps

 

[gemma-the-husky]

20000 records isnt many at all

did you say you dont have a separate server - so one of the terminals is actually the host for the backend?

in which case on this particular terminal you shouldnt get any performance issues at all

-------
how big are the various files now?

if your app isnt properly normalised it might be that the best solution is to bite the bullet , and re-engineer the database.

 

[antonyx]

hi, thanks for the response.. i have compacted and repaired the backend.. still the same issue is there..

with regards to normalizing the db that could be a problem.. mainly because of all the data that is already there. in two months we are changing our system all together so a huge re-design is not really necessary, what we need is a quick(ish) fix just so all my users can work at the same time and resolve any outstanding payments/bookings etc.

one thing i may be able to do (and im hoping there is a method to do this) is quickly replace half addresses with one version of a full address..

eg.

I have an address table that links to the job table using a numeric id..

for example. I might have three address records in my address table..

id: 554
The Mandeville Hotel

id: 41
The Mandeville Hotel London

id: 10045
The Mandeville Hotel, Mandeville Place, London, W1U 2BE


in this instance i would like to manually update any records with address field id 554 and 41, and use id 10045 (the full address).

i would then want the two non complete addresses to be deleted. how would i create an update query to do just that?

 

[antonyx]

Hi gemma,

the actual backend file is only 14.5mb at the moment (is that too big for a db with only 20-30 thousand records?), The database is not too bad in terms of normalisation. I would say it is atleast 75% normalised but a big issue is repetitive data as mentioned in my previous post.

and yes, one of the terminals is the actual host for the backend. the slowdown we experience on the main terminal is very minor (not an issue basically).

on the secondary terminals it is very bad. it can take up-to 30 seconds to load one form!

 

[gemma-the-husky]

might be to do with vista

look at ms knowledge base article 889588 - sorry cant do the link

[edited - got it now - this is the link]
http://support.microsoft.com/kb/889588

particularly the bit about sharing violation notification - this may be the issue

 

[antonyx]

that is very interesting, maybe i am not giving my database enough credit and the slowdown issues are actually os/network based

let me have a look through this and see what i can do..

 

[antonyx]

hi gemma, i have implemented the registry changes on the article link you provided and they have made dramatic differences.

one form which previously took 20+ seconds to load now loads fully in 3 seconds.. it is wonderful news. thank you so much. those adjustments really were crucial

 

[gemma-the-husky]

its not obvious at all is it - but it makes sense the way they explain it - you can actually see the delays/spikes if you monitor the os in task manager, while forms are loading - its even hard to find the knowledge base article by searching if you dont know the ref number.

i wasn't sure if this registry key was valid for vista

good luck