数据库压缩和归档 - MS Access 后端
场景：有一个遗留程序（不确定什么语言），我被要求“压缩并存档数据库中的表单”。当用户打开应用程序时，大约需要 2-5 分钟才能加载大约 27000 条记录！我的理论是它在启动时加载所有记录，但这可能不是唯一的原因。经过一番挖掘并找到看起来正确的访问后端后，我还在公司内 15 个以上的其他共享上发现了相同的访问文件。现在，这个应用程序是在 1997 年左右创建的，当时我猜测 Access 已成为常态，但他们真的会从 15 个以上的 Access 数据库中获取数据吗？加速该程序的标准似乎是将旧记录存档在另一个访问数据库中（这就是为什么我认为它在启动时加载所有内容。
问题：我有一个周一开会讨论该计划，想知道是否有人可以提出一些有用的问题、理论、解决方案等。这并不是说我不能自己做到这一点，我只是认为另一种观点也没什么坏处。事实上，我可能无法获得源代码，因为它可能是由承包商创建的，并且代码很久以前就丢失了
旁注：是否可以访问自动。存档旧记录？这意味着将它们转移到另一个名为 XXXArch 的数据库。
我会先尝试回答您的任何问题
：
情况。
这看起来是唯一的 使用一个数据库作为主数据库，一个数据库用于存档，我仍然没有自己的用户帐户来打开应用程序，但是在查看数据库时，有一个包含登录 ID 和相同密码（PASSWORD）的用户表，所以我尝试了以这些用户之一的身份登录并简单地选择一些数据而不修改任何内容。当选择时，我几乎能够立即获取数据，并且没有看到其他用户所遇到的任何放缓。我还没有看到源代码，但据我所知（获取 exe 并将其放入记事本中），它看起来像是用 VBA 编码的，并且可能是使用 MS Access 创建的。此外，应用程序似乎在数据文件夹中创建了一个 temp.mdb。目前它里面什么也没有。没有桌子，什么都没有。我假设/希望这是减慢用户速度的原因，可以将其删除以提高性能。一旦获得源代码并更好地了解是什么减慢了速度，我将发布另一个更新。
Scenario: There is a legacy program (Not sure what language) and I have been asked to "Compact and Archive forms in the database". At the moment when the user opens the application it is taking about 2-5min to load around 27000 Records!!! My theory is that it is loading all the records on start-up but that might not be the only reason. After doing some digging and finding an Access Back end that looks correct, I also found the same access files on 15+ other shares within the company. Now this application was created sometime around 1997 when I'm guessing Access was the norm, but would they really be grabbing data from 15+ Access databases? What seems to be the norm to speed up this program is to archive the older records in another access database (which is why I'm thinking it is loading everything at start-up.
Question: I have a meeting on Monday to discuss the program and was wondering if anyone could suggest some useful questions, theories, solutions, etc. It's not that I can't do this on my own, I just think another perspective couldn't hurt. Also another fun fact is that I may or may not be able to get the source code because it may have been created by a contractor and the code lost long ago.
Side Note: Would it be possible for Access to auto-archive old records? That would mean transferring them to another DB called XXXArch.
Thanks in advance. I will try to answer any questions you have.
EDIT:
Heres an update on the situation.
It looks like its only using one database as the main and one to archive. I still have yet to have my own user account to open the application but when looking into the database there is a user table with the login ID and the same password(PASSWORD) so I tried logging in as one of those users and simply selecting some data not modifying anything. When selecting I was able to get data almost instantly and wasn't seeing any of the slowdown that the other users were getting. I still haven't seen the source code but from what I can tell (taking the exe and putting it in notepad) it looks like it was coded in VBA and probably created using MS Access. Also it seems that the application creates a temp.mdb in a data folder. Currently it has nothing in it. No tables, nothing. I'm assuming/hoping that this is what is slowing the users down and can just be deleted to improve performance. I will post another update once I get the source code and have a better idea of what is slowing it down.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
需要考虑的几件事：
Access (MDB) 数据库如果经常使用，往往需要定期压缩/修复，正如您在标题中指出的那样。然而我很少发现它对性能的帮助超过最低限度。如果时间已经很长，文件可能会膨胀得很大，如果用户通过慢速网络连接访问它，这可能是问题的一部分。
有人会建议升级到“更大”的数据库，例如 SQL Server，无论是在您的公司还是在这个论坛中。在隔离问题之前或者除非有性能以外的原因之前，请勿这样做。这些问题很可能是由不良的应用程序设计或数据库架构引起的。在不改变方法的情况下使用更强大的工具来解决问题不太可能有帮助。
Access DB 早在数据达到极限之前就已经达到了并发用户数的极限。是否有很多用户（30+）刚刚开始使用该系统？这可能是问题的一部分。
归档旧记录：您将必须构建一些东西来做到这一点。好消息是这并不那么难。
访问 15 个以上数据库：您确定前端 GUI 不是用 Access 编写的吗？它是一种通用架构，可以将 MDB 前端加载到最终用户的计算机上（到处复制），连接到网络上的中央 MDB 数据文件。最好的判断方法是打开数据库，看看它们是否只包含表格，或者表格+表格/报告。
A couple of things to consider:
Access (MDB) databases tend to need regular compact/repair as you noted in the title if they are in frequent use. However I've rarely found that it helps performance more than minimally. If it has been a really long time the file can bloat really big and that might be part of the issue if users are accessing it over a slow network connection.
Someone is going to suggest upgrading to a "Bigger" DB like SQL server, either in your company or in this forum. Don't do that until you have isolated the problem or unless you have a reason other than performance. There is a reasonable chance the problems are caused by poor application design or DB architecture. Throwing a more powerful tool at the problem without changing the approach is unlikely to help.
An Access DB is going to max out on concurrent users long before it maxes out on data. Did a lot of users (30+) just start using the system? That could be part of the problem.
Archiving old records: You are going to have to build something to do this. The good news is that it isn't all that hard.
Accessing 15+ databases: Are you sure the front end GUI isn't written in Access. It is a common architecture with access to have an MDB front end loaded on the end user's machine (copied everywhere) connecting to a central MDB data file out on the network. The best way to tell is to open the databases and see if they contain just tables, or tables + forms/reports.
在我看来，您的首要任务应该是解决这个问题：
我可能无法获得源代码，因为它可能是由承包商创建的，并且代码很久以前就丢失了。
就目前情况而言，您要求我们推测缓慢的原因和补救措施......而不了解实际情况。
Seems to me your first order of business should be to resolve this issue:
I may or may not be able to get the source code because it may have been created by a contractor and the code lost long ago.
As it stands now, you're asking us to speculate about the causes of, and remedies for, the slowness ... without any knowledge about what's actually going on.
如果没有源代码，则无法将后端数据库更改为 SQL Server 或其他任何数据库。
但是，如果您确实有权访问数据文件并且能够编辑它们，为什么不检查索引呢？ 27K 记录对于任何数据库（包括 Access）来说都是微不足道的，而且加载数据的缓慢对我来说表明这些表根本没有正确索引。如果您检查表并发现明显字段上没有索引，则尝试添加它们并查看是否可以加快速度。
如果没有，则意味着该应用程序设计得很糟糕，并且由于您缺乏源代码，因此您对此无能为力。
当然，以上所有内容都假设网络环境适合 Access/Jet/ACE。也就是说，如果这些数据库文件是通过有线 LAN 以外的任何方式访问的，那么就无能为力（WAN 和 WiFi 完全不适合 Jet/ACE）。
最后，关于归档问题，我认为没有理由永远归档数据，除非您确实遇到了硬件/软件的硬限制。在这种情况下，你还差得远。
If you don't have source code, you can't change the back-end database to SQL Server nor anything else.
However, if you actually do have access to the data files and are able to edit them, why not check the indexing? 27K records is trivial for any database, including Access, and slowness in loading the data suggest to me that the tables are simply not indexed properly. If you examine the tables and see no indexes on obvious fields, then try adding them and see if it speeds things up.
If it doesn't, then it means the app is badly designed and since you lack source code, there's not a whole lot you can do about it.
All the above assumes, of course, that the networking environment is appropriate for Access/Jet/ACE. That is, if these database files are being accessed across anything other than a wired LAN, then there's nothing can be done about it (WAN and WiFi are completely out for Jet/ACE).
Last of all, on the subject of archiving, I think that there is no justification for ever archiving data unless you really are running up against hard limits on hardware/software. In this case, you're not even close to that.