是什么导致我的访问数据库太慢? [英] What is causing my access database to be so slow?

问题描述

只是为了澄清它实际上不是我的数据库，并且我没有选择访问权限，我只是通过开发一些已经实施的访问权限数据库来帮助一家公司.

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.

这些表单上有一些子表单，并且没有在后台运行的VBA脚本可能会导致无休止的循环，我为此而烦恼

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

我已关闭自动名称更正功能，由于其中一个文本框有一些分组，其中一个提示记录集无法编辑"，但是即使按照我的方式工作，它仍然运行得很慢

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

推荐答案

Tony Toews的访问权限我认为效果常见问题解答是最好的起点.

也就是说，您描述的问题听起来好像分为两类:

That said, the problems you describe sound like they fall into two classes:

A.慢速打开表格.

B.执行缓慢的表格.

B. slow performing forms.

A的两个常见原因:

使用现有用户为其创建LDB文件/争用的

1. .通常可以通过 Tony的LDB锁定文章中的某种形式的解决方案解决此问题. .如果打开第一个表单的速度很慢，而打开第一个表单的速度很慢，则打开后继的表单并不慢，您可以判断出是否是问题的原因. FWIW，我不使用该方法，而是使用一个完成相同任务的持久数据库变量(不是我最近一次在SO上发布代码，但是也许具有最佳上下文的代码在这里: MS Access:与DBEngine(0)(0)相比，使用CurrentDB时会产生大量开销吗?).

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

元数据已过期.例如，如果您在测试服务器的前端工作，将其移至生产环境并更新连接字符串以指向生产后端，则可能发生这种情况.更新连接字符串不会刷新存储在链接表定义中的所有元数据，也无法完全刷新它们.因此，您必须在生产环境中删除并重新创建链接表.这一个的症状是，在测试环境中，表单立即打开或仅在一两秒内打开，而在生产环境中，则需要一分钟或更长时间才能打开.打开后，它们通常可以正常工作. FWIW，除了在Access 2000成立之初，这是一个重大而可怕的问题，几乎使我失去工作(我的第一个A2000项目)之后，我才真正看到过这个问题.

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

性能低下的表单修复起来更复杂，但是原因通常并不复杂:表单一次加载太多数据.带有大量子表单(通常在选项卡控件上)和大量大型组合框的表单是常见的罪魁祸首.解决方案是在实际显示子窗体/组合框之前，不要加载它们.在选项卡控件中，这意味着为选项卡控件的OnChange事件中的每个选项卡加载子窗体.对于组合框，您需要在它们显示时加载它们，或者如果它们中有太多记录(我说要超过1000)，请在用户键入1或2个字符之后再加载行源(使用组合框的OnChange事件.

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

问题是，您要用一个主要的减速(在第一次打开表单时加载所有东西)来交换许多小的减速(根据需要加载每个子表单/行资源).这是一个折衷方案，您必须确定要在哪里痛苦.

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.

还有很多其他事情要做，而在解决性能问题时要尽早检查的一件事是每个主表单的Recordsource.左联接可能会在性能方面带来非常昂贵的开销，最好删除所有不是绝对必需的联接.最近，我大大加快了从子表到父表的左联接的窗体的工作.在子表的PK字段中，如果没有父ID，子就不可能存在，因此完全不需要左联接.删除它确实加快了从记录到记录的导航.

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.

这篇关于是什么导致我的访问数据库太慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！