Jim Dettman
jimdettman at earthlink.net
Sat Mar 1 09:58:00 CST 2003
Mark, This is a known issue. Part of the problems is OPLOCKS or opportunistic locking as Drew already mentioned. The other is the lock manager, which under NT/Win 2000 performs poorly. The simple fix is that as soon as the app opens, open a reference to the BE and close this reference when the app completes. This can be as simple as opening a hidden form bound to a dummy table in the BE. The other issue here is virus scanners that check a file each time it's opened. When all references on a BE go out of scope, it's closed. Next time it's needed it's reopened and this sometimes causes a virus scan to occur. Make sure your virus scanner is not checking MDB files. Take a look at the following MSKB article which also offers some other pointers: Q300216 - HOW TO Keep a Jet 4.0 Database in Top Working Condition http://support.microsoft.com/default.aspx?scid=kb;[LN];Q300216 Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Mark L. Breen Sent: Saturday, March 01, 2003 5:26 AM To: accessd at databaseadvisors.com Subject: [AccessD] Access & Windows 2000 Performance issues with linked tables Hello All, I was asked recently to move a database that I created three years ago from a Win NT 4.0 Server server to a new Windows 2000 Server server. The database application that I developed is built on a FE, a BE and a MDA file and also a .mdw file. When the backend is on the NT server, performance is fine, but when I move it to the new Windows 2000 server ( a super duper server with loads of disk / ram / processor ), it grinds to a halt. To make a long story short, it appears that Microsoft have introduced something to the Win2K and WinXP OS's that cause linked tables to perform very slowly. There is an article in the KB about this. Their suggestion (as opposed to the fix) is instead of talking to the properties of the linked tables, to programatically open the backend and talk directly to the properties of the source tables. In my case, I would have to re-write the entire application and it is not ecomomical to do so, a better alternative to that would be simply to rebuild the app in SQL and use ADO. However, I had another idea which was to bring the BE back into the FE and leave it as one .mdb file. When I tried that it solved my problem. The purpose of this email is to share the information with you guys and ask if you have experienced this also. Incidently, the 'normal' operations such as reading data, querying etc, was never impaired, it was only when talking to linked tables, If you wish to demonstrate this for yourself, create a db with one table and with about eight fields or so. Save the db as Somename_BE.mdb on a > = Win 2k machine, it can be Pro or Server. Then create another db and link to Somename_BE.mdb. Finally, let the form wizard create a new form and pull all the fields in. The form creatation should talk you about ten seconds or so. If you do the same thing on a Win NT Server, it happens in one second. I do not know if this will help anyone, but hopefully it might. Best Regards Mark L. Breen Solution Providers Ltd Ireland _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com