Everything I ever needed to know I learned in kindergarten, right? Well, I used to think so, and used to be a pretty gosh darn good sharer, until suddenly I was using Jet replication and discovered that sometimes being a good sharer can be a bad thing for the performance of your application. What do I mean? Well, read on...
The scenario is pretty straightforward--you have an application that is working well, you replicate it, and suddenly, every time you do something simple like open a table, performance becomes abysmal... sometimes it whirs the floppy drive or the CDROM drive every
time. You're ready to pull your hair out, or (if you have no hair) ready to unreplicate the database, unless you can make it stop!
Well, the answer is simple.... unshare your drives!
Yes, that's right. Due to the way that Microsoft Jet's replication tracking layer manages information on whether replicas such as
H:\foo.mdb (<--mapped to one of the UNC paths above)
are in fact the same replica, it does some work (some might say too much work!) to disambiguate the share info on the machine so that it knows what all the shares are. Unfortunately, due to the way Access implements some of *its* jobs and how it calls Jet to do them, this "detection" can happen a lot more often than you might want it to. :-(
For shared connections that go to other machines, especially if the other machine is unavailable, the internal calls it makes to WNet* functions can have a perf hit. For local shares, the hit is not so bad, unless you are sharing a CDROM or a floppy drive, in which case the perf hit is once again noticeable and actually can be quite annoying.
So how do you combat this problem? Whenever possible on a machine using an application that uses replication, try not to share out CDROM drives or floppy drives, and try to avoid mapping drives to network connections, especially slow ones (use UNC paths instead). This will prevent you from suffering the awful performance hit that can be associated with being a good sharer. :-)