I have a C# application that uses an MS Access database (.mdb file). I share my database with network users so they can access the database with my application.
When I run my application it takes longer to execute the query on the network database, while that same query takes less time on my local machine.
Can any one provide advice on how to reduce this performance issue with an Access database on a network share?
Of course queries accessing a database over the network are not going to be as fast as the same queries accessing a database on the local hard drive. Unfortunately it is fairly common for a developer to build an application that runs okay locally, but slows significantly once it has been deployed on the network. (This can be true for any developer, not just ones using Access databases.)
In my experience, the three most important things to do regarding the performance of an application using a shared Access database are:
Table scans are murder on a shared-file database like Access. Be sure to have indexes on fields that are used in WHERE clauses or are used to JOIN tables.
To illustrate, I ran the following command on a 122 MB .accdb file containing one table with 421,184 rows:
cmd.CommandText =
"SELECT COUNT(*) AS n FROM zz_sys_archive " +
"WHERE archived Between #2013-01-01# And #2013-04-01#";
With no index on the [archived] field the command took 78 seconds to execute and generated 107 MB of network traffic.
After adding an index to the [archived] field that same command took 0.4 seconds to execute and generated 0.9 MB of network traffic.
(However, don't go nuts and index everything because extraneous indexes will just slow down INSERT and UPDATE operations.)
Even with appropriate indexes in place a poorly-designed query will result in table scans and slow your application down. For example, the query
cmd.CommandText =
"SELECT COUNT(*) AS n FROM zz_sys_archive " +
"WHERE Year(archived) = 2013";
is not sargable, meaning that it cannot use the index on the [archived] field and does a table scan with the same results as before (~80 seconds to complete). However, the equivalent query
cmd.CommandText =
"SELECT COUNT(*) AS n FROM zz_sys_archive " +
"WHERE archived >= #2013-01-01# AND archived < #2014-01-01#";
took about one second to execute.
With a local database it is often tempting to just read the whole table and ignore what you don't really want. Accessing a database over the network makes that much more expensive, so think about what you really need before you just "SELECT * FROM wherever".