Front end extremely slow with BE on server

[gauntletxg]

I have a customer database with 20 tables, and it is split between a front end and a back end. In total there are roughly 50,000 records between all of the tables. There are 6 users, and at most 2 or 3 people use it at once. Every morning when the user logs on the front end is copied from a server to the users hard drive, and they access the database through this local version. The back end remains on the server.

I do all of the database development at home away from the job site, and experience absolutely no problems with speed. However everyone who uses it in a live environment complains about it going really slow. I spent a great deal of time optimizing my code, getting rid of redundant queries, double-checkings the indexes, etc. etc., all to no avail.

I went on site yesterday, and things are horribly slow. In some instances it takes 3-4 minutes to just close one form and open another. I kept thinking I missed something big in my development, but then I tried to just relink the tables from a users local front end to the server on the back end - ten minutes!!! I moved the back end to the users hard drive, and relinking the tables goes almost instantly, and the database performs about as fast as it does as my machine at home. But the moment you link to the server everything bogs down tremendously.

The server is less than 6 months old, so I don't think it's a case of the server not having the requisite capabilities. Any thoughts or suggestions on steps I can take to improve the speed? Eventually I plan to migrate the backend to SQL server, which will hopefully help, but the system needs to be functional in the meantime as the conversion could be a few months away. I've exhausted all of the avenues I'm aware of.

Any input is greatly appreciated.

 

[Norie]

Why each morning is the front end being copied to the local machine?

That doesn't seem to make a lot of sense, and it might be something to do with the problem you are experiencing.

Other than that the only thing I can think of, off the top of my head, is that it's some sort of network problem.

But you say the server is new etc so that might be wrong.

 

[gauntletxg]

It's (I think?) the easiest way for me to deploy new versions out to each user. Basically how it works is:

- There's code in the front end that checks for the existence of a text file once every 5 minutes. If the file isn't there it kicks the user out of the database. As part of the nightly backup routine this file is programtically deleted to make sure everyone has logged out because people will sometimes sit in the main menu after they leave for the day. This way we don't backup possibly corrupt data. I can also then delete the text file from home to kick people out if I need to make a change on the server when everyone has left for the day.

- The front end is then copied from the server to the local hard drive in the morning. Most of the time this is redundant, as I only upload new versions of the front end maybe once every 2 weeks.

- When I make changes to the front-end I just put the new version onto the server from home using GoToMyPC. That way in the morning the latest version is automatically copied to each persons hard drive, and I don't have to bother with having someone at the office do all of the work of copying files over.

This process has been in place since I started development about a year ago, so it's not something I've put a lot of thought into since.

I will try to get some info on the server later today. I wonder if there are any benchmark type tests out there to see if something is going screwy with the network?

 

[revver]

It certainly sounds more like a network problem than an Access one but if that were so then other network operations would similarly be slow.

Is it a peer to peer network with one peer called a server or is it in fact a domain setup? How many PCs?

As a place to start, on the server bring up task manager and monitor the Networking tab both while the back end is being used and while it isn't. You could also look at the Performance tab.

The Processes tab (sorted descending by CPU) will show you what is keeping the server busy if that's what the problem is. Make sure that the check box in bottom left "Show processes from all users" is checked.

The result of these checks will tell us where to look next. You might even have a PC whose network card has gone rogue and is choking the network with jamming traffic.