慢速网络上的MS Access数据库:分离后端是否更快? [英] MS Access databases on slow network: Is it faster to separate back ends?

问题描述

我有一个Access数据库，其中包含有关人员的信息(员工资料和相关信息).前端有一个类似于控制台的界面，可一次修改一种类型的数据(例如，一种形式的学历，另一种形式的联系信息).当前，它链接到多个后端(一种用于每种数据类型，另一种用于基本配置文件信息).所有文件都位于网络共享上，并且许多后端都经过加密.

I have an Access database containing information about people (employee profiles and related information). The front end has a single console-like interface that modifies one type of data at a time (such as academic degrees from one form, contact information from another). It is currently linked to multiple back ends (one for each type of data, and one for the basic profile information). All files are located on a network share and many of the back ends are encrypted.

之所以这样做，是因为我知道MS Access必须将整个数据库文件拉到本地计算机上才能进行任何查询或更新，然后将所有更改的数据放回网络共享中.我的理论是，如果一个人要更改电话号码或地址(联系信息)，则只需拉/修改/替换联系信息数据库，而不必拉一个包含联系信息，项目，学位，奖项的大型数据库等只是更改一个电话号码，从而减少了当多个用户访问数据时锁定数据库和网络流量的可能性.

The reason I have done that is that I understand that MS Access has to pull the entire database file to the local computer in order to make any queries or updates, then put any changed data back on the network share. My theory is that if a person is changing a telephone number or address (contact information), they would only have to pull/modify/replace the contact information database, rather than pull a single large database containing contact information, projects, degrees, awards, etc. just to change one telephone number, thus reducing the potential for locked databases and network traffic when multiple users are accessing data.

这是一个理智的结论吗?我会误会很多吗?我还想念其他东西吗?

Is this a sane conclusion? Do I misunderstand a great deal? Am I missing something else?

我意识到每个文件都需要考虑开销，但是我不知道影响有多大.如果我要整合后端，则还有潜在的好处，就是能够让Access处理级联删除等的参照完整性，而不是为此编码...

I realize there is the consideration of overhead with each file, but I don't know how great the impact is. If I were to consolidate the back ends, there is also the potential benefit of being able to let Access handle referential integrity for cascading deletes, etc., rather than coding for that...

我将不胜感激任何想法或(合理合理的)批评.

I'd appreciate any thoughts or (reasonably valid) criticisms.

推荐答案

这是一个常见的误解:

MS Access必须将整个数据库文件拉至本地计算机，以便进行任何查询或更新

考虑此查询:

SELECT first_name, last_name
FROM Employees
WHERE EmpID = 27;

如果对EmpID进行了索引，则数据库引擎将只读取足够的索引以查找匹配的表行，然后读取匹配的行.如果索引包含唯一约束(例如EmpID是主键)，则读取速度会更快.数据库引擎不会读取整个表，甚至无法读取整个索引.

If EmpID is indexed, the database engine will read just enough of the index to find which table rows match, then read the matching rows. If the index includes a unique constraint (say EmpID is the primary key), the reading will be faster. The database engine doesn't read the entire table, nor even the entire index.

在没有EmpID索引的情况下，引擎将对Employees表进行全表扫描---这意味着它必须从表中读取每一行，以确定哪些行包含匹配的EmpID值.

Without an index on EmpID, the engine would do a full table scan of the Employees table --- meaning it would have to read every row from the table to determine which include matching EmpID values.

但是无论哪种方式，引擎都不需要读取整个数据库……客户，库存，销售等表格……它没有理由读取所有数据.

But either way, the engine doesn't need to read the entire database ... Clients, Inventory, Sales, etc. tables ... it has no reason to read all that data.

您正确的是，与后端数据库文件的连接存在开销.引擎必须为每个数据库管理一个锁定文件.我不知道这种影响的严重性.如果是我，我将创建一个新的后端数据库，并从其他数据库中导入表.然后复制前端并重新链接到后端表.这将使您有机会直接检查性能影响.

You're correct that there is overhead for connections to the back-end database files. The engine must manage a lock file for each database. I don't know the magnitude of that impact. If it were me, I would create a new back-end database and import the tables from the others. Then make a copy of the front-end and re-link to the back-end tables. That would give you the opportunity to examine the performance impact directly.

在我看来，关系完整性对于将表整合到单个后端应该是一个有力的论据.

Seems to me relational integrity should be a strong argument for consolidating the tables into a single back-end.

关于锁定，您永远不需要为常规DML(INSERT，UPDATE，DELETE)操作锁定整个后端数据库.数据库基础引擎支持更精细的锁定.悲观锁定还是机会锁定-锁定是在您开始编辑行之后发生还是推迟到保存更改的行之后才发生.

Regarding locking, you shouldn't ever need to lock the entire back-end database for routine DML (INSERT, UPDATE, DELETE) operations. The database base engine supports more granular locking. Also pessimistic vs. opportunistic locking --- whether the lock occurs once you begin editing a row or is deferred until you save the changed row.

如果慢速网络"意味着无线网络，那么实际上慢速网络"可能是最大的问题.仅在有线局域网上访问才是安全的.

Actually "slow network" could be the biggest concern if slow means a wireless network. Access is only safe on a hard-wired LAN.

编辑:访问不适用于WAN网络环境.请参见Albert D. Kallal的本页.

Edit: Access is not appropriate for a WAN network environment. See this page by Albert D. Kallal.

这篇关于慢速网络上的MS Access数据库:分离后端是否更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！