0

We have an MS-Access DB on a file share. We're getting slow performance and error messages we suspect have to do with file access being so slow. What would be the first things to check to find out what the bottle neck is?

It works fine locally, so we're pretty sure it isn't the application itself.

MatthewMartin
  • 289
  • 1
  • 6
  • 14
  • Assuming that you are seeing the decreased performance when doing searches/queries, does the file also respond slowly when you apply a filter on a form? – Jeremy Viet Jul 06 '09 at 19:52
  • Some info on where the share is would be useful - how much bandwidth does the network link actually have [Evan's test is a good idea below] and how high is the latency between the share and the users [Ping time]. Gigabit networks on a healthy LAN will be faster than an average local hardrive but 100Megabit network links are a lot slower. However if the latency of your network link is high (Ping time / Round Trip Time > a couple of milliseconds) then even a Gigabit network will fail to match the IO per second performance of a relatively average local drive. – Helvick Jul 06 '09 at 20:14

4 Answers4

5

You can do a "quick and dirty" test for file server throughput by creating a large temporary file on the server computer with the fsutil command, and then timing the transfer to a client computer:

fsutil file createnew temp-file-name 209715200

That would create 200MB temporary file. You can do a quick copy w/ timing using the following script (from the directory where you created the temporary file on the server, and assuming you have rights to connect to the "C$" share of the client computer):

@echo off
echo.|time
copy temp-file-name \\remote-computer-name\c$
echo.|time

Subtract the ending time from the starting time, convert to seconds, and divide 209715200 by the number of seconds elapsed to get bytes-per-second.

You should see upwards of 7,000,000 bytes per second (roughly 56Mbps) on a 100Base-TX LAN. Anything below that and I'd begin to suspect that something is up. Assuming that the server computer is reasonably modern, it should be able to fill a 100Mbps pipe with no problem. If you're seeing transfer speeds slower than that, I'd start to look at the error counters in the administration interface of the switch that the server and client are connected to. You could have faulty cabling, a duplex mismatch, or NIC driver problems. It's all just a matter of tracking the problem down methodically.

Evan Anderson
  • 141,071
  • 19
  • 191
  • 328
1

Try making the filename shorter and the full path shorter. Sounds strange, but true in some setups. See this KB.

You could also make sure SMB signing is disabled (HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Lanmanworkstation\Para meters\enablesecuritysignature set to 0) on both the server and workstation (see this thread).

There is also a somewhat strange issue related to LDB locking that you can test for. See this page.

Adam Brand
  • 6,057
  • 2
  • 28
  • 40
1

See the Access Performance FAQ page.

Tony Toews
  • 352
  • 1
  • 10
0

A couple of other things to try would be to compact the database itself (I assume Access still has this function) and defrag the actual database file on the machine doing the sharing. For defragging single files, I recommend the sysinternals contig command line utility.

You could also test for a bad network, by running ping for an extended time (I believe "ping -t" is the correct Windows incantation of the command), and seeing if you're dropping packets or seeing high network latency.

Geoff Fritz
  • 1,717
  • 9
  • 11