 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / March 2004Performance Issues
Could I sound you out on a performance issue that has me puzzled. In order to share a complex database over a network I have designed it to use Tables on the Server with separate front-ends on each PC linked to Tables on the Server. In practice I have found this to be disastrously slow. It is so slow loading that I have simply stopped trying to let it load. I’m not sure whether it is the number of linked Tables we have – there are 95 ! Instead we have resorted to sharing the one database on the server. Any ideas When sharing a database on the Server we have noticed that it is about the same speed as when it is run on the local PC if only one person is logged into it. If however more than one person is logged in, then whether they are actually using the system or not, it can be up to four times as slow. We have looked at network traffic and on a 100Mbit network it is only running at 35% of capacity. We have also tried a 1Gbit switch and network cards – again no improvement. We have looked at CPU usage and have found that at times it peaks at 100% capacity. So you would think that if it ran on a faster CPU it would go quicker. However this is not the case. It runs at exactly the same speed on a PIII 733 Mhz, a P4 2.0Ghz, and a Celeron 2.4Ghz. So it must be the disk that it limiting it. Again not so the disk is almost inactive throughout. So what the hell is going on We DO have a project in hand to convert to MSDE, but in the meantime would like to wring out every last bit of perfomance from Access on its own. Any assistance would be greatly appreciated >Could I sound you out on a performance issue that has me puzzled. In order to share a complex database over a network I have designed it to use Tables on the Server with separate front-ends on each PC linked to Tables on the Server. In practice I have found this to be disastrously slow. It is so slow loading that I have simply stopped trying to let it load. Im not sure whether it is the number of linked Tables we have there are 95 ! No. I have a client with 150 linked tables and performance is fine. > Instead we have resorted to sharing the one database on the server. Any ideas? You may end up with corruptions in this situation. >When sharing a database on the Server we have noticed that it is about the same speed as when it is run on the local PC if only one person is logged into it. If however more than one person is logged in, then whether they are actually using the system or not, it can be up to four times as slow. Standard problem. Standard blurb follows. The three most common performance problems in Access 2000 are: - LDB locking which a persistent recordset connection or an always open bound form corrects (multiple users) - sub datasheet Name property set to [Auto] should be [None] - Track name AutoCorrect should be off If the problem is for everyone when starting up the MDB then it likely needs a decompile. For more information on these, less likely causes, other tips and links to MS KB articles visit my Access Performance FAQ page at http://www.granite.ab.ca/access/performancefaq.htm Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm > So what the hell is going on? You are watching either a network time-out or a file-server time-out. Network time-outs happen when you consolidate small packets into larger ones (to reduce network loads). File System time-outs happen when you try to lock files and parts of files on the server. 'Optimisations' of Access and Windows make these problems much worse than they were when Access was first designed: Hardware improvements make these software problems much more prominent than they were when Access was first designed. (david) > Could I sound you out on a performance issue that has me puzzled. In order to share a complex database over a network I have designed it to use Tables on the Server with separate front-ends on each PC linked to Tables on the Server. In practice I have found this to be disastrously slow. It is so slow loading that I have simply stopped trying to let it load. I'm not sure whether it is the number of linked Tables we have - there are 95 ! Instead we have resorted to sharing the one database on the server. Any ideas? First, linking tables is a one time event, and should not and does not occur very often. Why are you not pre-linking before you distribute the front end? And, further...YOU REALLY need to be distributing a "mde" for the front end. Generally, I pre-link the front end BEFORE I distribute it to my users. However, I see re-linking times drop down from about 6 or 7 minutes to about 20 seconds using Tony's trick: That trick: When re-linking, link to the first table, and then OPEN it. Then, continue re-linking the rest. This of course assumes you are using your own re-linking code (which most of the developers here use anyway). > When sharing a database on the Server we have noticed that it is about the same speed as when it is run on the local PC if only one person is logged into it. If however more than one person is logged in, then whether they are actually using the system or not, it can be up to four times as slow. Yes, the system has to switch from single user file access mode to multi-user access mode. Huge amounts of things occur when the network decides to go from single user mode to multi user mode (things like flushing of local buffers etc). Further, there is the nasty ms-access locking file...and that is usually the #1 problem. You need to force, or keep open a persistent connection to the back end at all times. I have a small application with 60 tables, and each table is VERY VERY small, ...only about 40,000 records on average is in each table. With 5 users on a cheap office network, response time is absolute instant, and their is virtually NO delay in running this application. Of course...5 users, and such small data tables is not much, but I have no doubt that if the tables where 3 x and the I had 15 users...the performance and results would be the same. So, 15 users, and again real small tables with only 120,000 records should be nothing for your network... However, 9 out of 10 times...keeping a persistent connection to the back end fixes this stuff. This stops ms-access from creating, and then deleting the locking file on the server (you then find performance slows down to the speed at which the server can create a file, or delete a file...and that can be VERY slow). when you keep the connection open..this file create does not occur...and your performance will just fly.... Check out, and read Tony's faq: http://www.granite.ab.ca/access/performancefaq.htm -- Albert D. Kallal (MVP) Edmonton, Alberta Canada pleasenonoSpamKallal@msn.com http://www.attcanada.net/~kallal.msn > locking file on the server (you then find performance slows down to the > speed at which the server can create a file, or delete a file...and that > be VERY slow). when you keep the connection open..this file create does > not occur...and your performance will just fly.... Another Windows optimisation. Create/Delete is a rare operation, whereas re-open is common. So the file system tends to keep files open after last use. You have to wait for that to time out before you can delete. On create, you have to check the file permissions, including the inherited permissions from the whole directory tree. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.