Javascript 是什么导致我的访问数据库如此缓慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3716639/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is causing my access database to be so slow?
提问by Harold
Just to clarify it's not actually my database and I didn't choose access, I'm just helping a company out by developing some of their already implemented access database.
只是为了澄清它实际上不是我的数据库，我没有选择访问，我只是通过开发一些他们已经实施的访问数据库来帮助公司。
Anyway, on some of their forms the forms open and run extremely slowly for no apparent reason. There is one form which takes a long long time to open on everyone's computer but runs fine when it's there and there's another form that runs fine on most people's computers and is practically unusable on a few.
无论如何，在它们的某些表单上，表单无缘无故地打开和运行非常缓慢。有一种表格需要很长时间才能在每个人的计算机上打开，但是当它在那里时运行良好，还有另一种表格在大多数人的计算机上运行良好，但在少数人的计算机上几乎无法使用。
The forms have a few subforms on them and no VBA script running in the background that might cause an endless loop and I am stumped for ideas
这些表单上有一些子表单，并且没有在后台运行的 VBA 脚本可能会导致无限循环，我对想法感到困惑
I have turned auto namecorrect off and it came up on one of them saying "The recordset cannot be edited" because of some grouping on one of the text boxes but even when I worked my way around that it still ran just as slow
我已经关闭了自动名称更正，其中一个说“无法编辑记录集”，因为其中一个文本框上有一些分组，但即使我解决了它，它仍然运行得很慢
回答by David-W-Fenton
Tony Toews's Access Performance FAQis the best place to start, in my opinion.
在我看来，Tony Toews 的 Access Performance FAQ是最好的起点。
That said, the problems you describe sound like they fall into two classes:
也就是说，您描述的问题听起来像是分为两类：
A. slow opening forms.
A. 慢开形式。
B. slow performing forms.
B. 表现缓慢的表格。
There are two common causes for A:
A 有两个常见的原因：
creation of the LDB file/contention for it with existing users. This problem is usually resolved with some form of the solution from Tony's LDB Locking article. You can tell if this is the cause of the problem if opening the first form is slow, and opening successive forms are not slow if you leave the initial one open. FWIW, I don't use that method, but use a persistent database variable that accomplishes the same thing (not the most recent time I've posted the code on SO, but perhaps the one with the best context is here: MS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?).
out-of-date metadata in linked tables. This can happen if, for instance, you work on the front end on your test server, move it to the production environment and update the connect strings to point to the production back end. Updating the connect string doesn't refresh all the metadata stored in the linked table definitions, and there's no way to actually fully refresh them. So, you have to delete and recreate the linked tables in the production environment. The symptom of this one is that in the test environment forms open immediately or in only a second or two, and in the production environment take a minute or more to open. After opening, they generally work just fine. FWIW, I haven't really seen this problem except in the earliest days of Access 2000 when it was a significant and terrible problem that almost cost me a job (my first A2000 project).
与现有用户为其创建 LDB 文件/争用。这个问题通常可以通过Tony 的 LDB 锁定文章中的某种形式的解决方案来解决。如果打开第一个表单很慢，您可以判断这是否是问题的原因，如果打开第一个表单，打开后续表单并不慢。FWIW，我不使用这种方法，而是使用一个持久性数据库变量来完成同样的事情（不是最近一次我在 SO 上发布代码，但也许最好的上下文在这里：MS Access：与 DBEngine(0)(0) 相比，使用 CurrentDB 时是否有显着的开销？）。
链接表中的过时元数据。例如，如果您在测试服务器的前端工作，将其移动到生产环境并更新连接字符串以指向生产后端，就会发生这种情况。更新连接字符串不会刷新链接表定义中存储的所有元数据，并且无法真正完全刷新它们。因此，您必须在生产环境中删除并重新创建链接表。这个的症状是在测试环境表单立即打开或者只在一两秒内打开，而在生产环境中需要一分钟或更长时间才能打开。打开后，它们通常可以正常工作。FWIW，我没有
Slow-performing forms is more complicated to fix, but the cause is usually pretty uncomplicated: the forms are loading too much data at once. Forms with lots of subforms (usually on a tab control) and lots of large combo boxes are the usual culprit. The solution is to not load the subforms/combo boxes until they are actually displayed. In a tab control that means loading the subform for each tab in the tab control's OnChange event. For combo boxes, you'd load them when they are displayed, or if they have too many records in them (over 1000, I'd say), don't load a rowsource until after the user has typed 1 or 2 characters (using the OnChange event of the combo box).
性能缓慢的表单修复起来更复杂，但原因通常很简单：表单一次加载了太多数据。具有大量子表单（通常在选项卡控件上）和大量大型组合框的表单是常见的罪魁祸首。解决方案是在子窗体/组合框实际显示之前不加载它们。在选项卡控件中，这意味着在选项卡控件的 OnChange 事件中为每个选项卡加载子表单。对于组合框，您可以在显示它们时加载它们，或者如果它们中有太多记录（超过 1000，我会说），在用户输入 1 或 2 个字符之前不要加载行源（使用组合框的 OnChange 事件）。
The problem with that is that you're trading one major slowdown (loading all that stuff when the form is first opened) for a number of much smaller slowdowns (loading each subform/rowsource as its needed). It's a trade-off and you have to decide where you want your pain.
问题在于，您正在用一个主要的减速（在第一次打开表单时加载所有这些东西）来换取一些更小的减速（根据需要加载每个子表单/行源）。这是一个权衡，你必须决定你想要的痛苦。
There are lots of other things to do, and one thing to examine early on in troubleshooting performance problems is the Recordsource of each main form. Left joins can get really expensive performance-wise and it's a good idea to eliminate any of them that aren't absolutely required. I just recently vastly speeded up a form that had a left join to the parent table from the child table. It was impossible for the child to exist without a parentID in the PK field in the child table, so that left join was completely unnecessary. Removing it really speeded up navigation from record to record.
还有很多其他的事情要做，在解决性能问题时要尽早检查的一件事是每个主窗体的 Recordsource。左连接在性能方面可能会变得非常昂贵，最好消除任何不是绝对需要的。我最近才大大加快了一个从子表到父表的左连接的表单。子表的PK字段中没有parentID的子项不可能存在，因此完全不需要左连接。删除它确实加快了从记录到记录的导航。
回答by temo
Identify the recordsource from the form properties (table/query/sql statement) and run this directly on the affected machine/s. That will help to narrow down the problem as either form or recordsource. You may for example find that one of the forms is referencing a particularly slow query (large tables, multiple joins, dlookups etc) in which case you would need to focus on optimising this.
从表单属性（表/查询/sql 语句）中识别记录源并直接在受影响的机器上运行它。这将有助于将问题缩小为表单或记录源。例如，您可能会发现其中一个表单引用了一个特别慢的查询（大表、多个连接、dlookups 等），在这种情况下，您需要专注于优化它。
