C#程序查询网络文件夹中的Access数据库所需的时间比查询本地副本所需的时间长 [英] C# program querying an Access database in a network folder takes longer than querying a local copy

问题描述

我有一个使用MS Access数据库(.mdb文件)的C#应用​​程序.我与网络用户共享数据库，以便他们可以通过我的应用程序访问数据库.

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.

任何人都可以提供有关如何通过网络共享上的Access数据库减少此性能问题的建议吗?

Can any one provide advice on how to reduce this performance issue with an Access database on a network share?

推荐答案

当然，通过网络访问数据库的查询不会像访问本地硬盘驱动器上的数据库的查询那样快.不幸的是，对于开发人员来说，构建可以在本地运行的应用程序是相当普遍的，但是一旦将其部署在网络上，运行速度就会大大降低. (这对任何开发人员都是正确的，而不仅仅是使用Access数据库的开发人员.)

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.)

根据我的经验，与使用共享Access数据库的应用程序的性能有关的三个最重要的事情是:

In my experience, the three most important things to do regarding the performance of an application using a shared Access database are:

表扫描在诸如Access之类的共享文件数据库上是谋杀案.确保在WHERE子句中使用的字段或用于JOIN表的字段上具有索引.

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.

为说明起见，我在一个包含一个具有421,184行的表的122 MB .accdb文件上运行以下命令:

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#";

在[已存档]字段上没有索引的情况下，该命令执行了78秒，并产生了107 MB的网络流量.

With no index on the [archived] field the command took 78 seconds to execute and generated 107 MB of network traffic.

在[存档]字段中添加索引后，同一命令执行所需的时间为0.4秒，并产生0.9 MB的网络流量.

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.

(但是，不要胡乱地为所有内容建立索引，因为无关的索引只会减慢INSERT和UPDATE操作的速度.)

(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";

不是可修改，这意味着它无法使用[已归档]字段上的索引，并且用与以前相同的结果进行表扫描(大约需要80秒才能完成).但是，等效查询

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".

这篇关于C#程序查询网络文件夹中的Access数据库所需的时间比查询本地副本所需的时间长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！