Improve MS Access performance on a Win 2k LAN
We have recently replaced our old DataEase database with a package that was built using MS Access, and the system has slowed away down to an unbearable speed. However we are stuck with it now, and I would like to know if there is anything that we can do to improve the performance of the database.
The server is a Win 2k Server, P4 2.5GHz CPU, 256 Mb RAM with a Gigabit NIC. The minimum spec PC is on Win XP Pro, Celeron 2.2MHz with 256 Mb RAM. All the PCs are running 100 Mbps NIC.
Any tips or hinmts would be a great help.
Regards
Andrew H
Software/Hardware used:
Software/Hardware used:
ASKED:
March 1, 2005 7:01 AM
UPDATED:
March 2, 2005 10:03 AM
Answer Wiki:
Hi Harg7769,
I am assuming a relatively small wokgroup of around 10-25 users, in the absence of other information. There are possibly two issues here, an easy one and a difficult one. The easy one is to throw RAM at the problem - double the quantities on the workstations and quadruple on the server. You might also upgrade your switch/router connected to the server to gigabit and cables to cat 6 (5a at a pinch, but this degrades performance to around 300MB/s I understand) if it is not already, as you will not be gaining the benefit of gigabit on the server unless you do.
The hard part is the database. Access databases (from 2000 on if I recall) can be configured in one of two ways - one way is a monolithic entity, in which queries to the data generate significant traffic across the network because large chunks of database code are shuttled across the network along with the data, abd the other way is a client-server mode, (using a technology MS calls MSDE), in which the processing is done on the server, and only the query instructions and the data results are streamed over the connection, displaying the results on your desktop in a separate "front end" to the database.
I am not a DBA, so I cannot tell you how hard it is to convert one type to the other if your "stuck with" database is a monolithic one. Perhaps a DBA in the forum can help if that turns out to be a problem. In any event throwing RAM at the problem will solve a lot of ills in the database world.
Good luck.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi Microsolve,
Sorry, I should have said. Yes there are approx 25 users.
At the moment, the server is using the gigabit via Cat 6 direct connection to the switch on one of the two gigabit ports. All the PCs are running on Cat 5 cabling, although i’m not sure if it’s 5a or 5e at the moment. I have just bought a new PC with a gigabit NIC so I’ll try that and see if it registers at 100 Mbps or 1 Gbps. If it does then I can measure the speed of one PC for now.
Update: I have just tested a new PC which had a Gigabit NIC on the spare gigabit port on the switch and the difference was dramatic. This means that the cabling is 5e. So if I can persuade the boss that he needs to buy 24 Gigabit NICs and a 48 port gigabit switch, we should be ok.
Thanks for your help.
Hi Harg,
Thanks for your reply. I don’t want to be a bit of a damp squib here, but if your database is monolithic, there is a chance that the hoped for “dramatic” increase may not materialise quite as you hoped. Your Gigabit server pipe is well matched to around 10 concurrent 100Kb users. If you raise the workstation and all connections to 1Gb, you will still have a bottleneck in the server, which will lessen as the number of concurrent users does. You only tested one Gb user on your net while the reste were either throttled to 100Kb, or (if you tested after hours!) not there. If all 25 users are using the database concurrently, my guess is that little if any benefit will accrue from a blanket gigabit upgrade. In that case you might also want to consider upgrading the server to 10Gb. Not sure if that falls within your budget though! However, if concurrent use of the database is light, your proposed solution should work. More memory always helps database use though, and WinXP’s minimum useable loadout is 256MB, so you will get real performance gains in all software from another 256 on the w/s and definitely a substantial lift from the upgrading the server if it is spawning multiple database queries – even more if it is used as a print server too. Just re-checked your OS on the server: 256MB is OK for light duties, but 512 will provide the same kind of gains on 2K Server as it does on a WinXP client.
Hope this helps
Hi,
Reading the replies sofar, I want to add the following. assume the monolithic nature of your MS Access app. is a source of worry, which ultimately will keep slowing you down no matter what tehnical features you put underneath. If the app was bought outside try to persuede the manufacturer to convert it to a client server version (as suggested by others). If the app is “open” you can do that yourself by defining a project in MS Access and then asking to convert to a “client-server” type of structure. This involves MSDE, which is no other then a bare SQL server engine. This will hold your tables and queries whereas S Access willl hold the rest, i.e. forms etc. You can then also move your files to a “true” SQL server under windows 2k and up. More concurrent Queries will be handle with less strain on your system. I hope this helps.
Robert
Thanks again for your answers. The database has been split with the MDE file residing on the workstations and the MDB file holding all the tables residing on the server, so that part of it is working as it should be.
We have spoken to another company who are using the same app and they say that since they moved to a Gigabit LAN , the improvement in performance has been dramatic, so I’m going to give it a try with another PC now.
I realise that Access in this environment isn’t ideal (hence my recommendation not to buy the thing in the first place), but isn’t there some way of getting it to work in a satisfactory manner, other than upgrading to a SQL server? I knew it would come to this eventualy, but not a week after we have bought it.
Cheers
Andrew H
The other question is what version of access is being used? Some older versions had easily reachable maximum database sizes. Once the DB crossed this size limit the speed was affected drastically in a negative way.