Microsoft JET SQL 查询日志记录或“如何调试客户的程序？”
问题：
我们使用我们最大客户编写的程序来接收订单、预订运输以及执行其他与订单相关的操作。 我们别无选择，只能使用该程序，而当涉及到他们的程序出现问题时，客户非常不支持。 我们只需要接受这个计划。
现在，当与两个或更多用户一起使用时，该程序在大多数情况下都非常慢，因此我尝试查看幕后并找到问题的根源。
到目前为止我发现的有关该程序的一些要点：
- 它是用 VB 6.0 编写的
- 它使用受密码保护的 Access-DB (Access 2000 MDB)，该数据库位于用户计算机上的一个文件夹中。
- 该文件夹通过网络共享并由所有其他用户使用。
- 它使用 msjet40.dll 版本 4.00.9704 与访问进行通信。 我猜是ADO吧？
我还使用 Process Monitor 来监视文件访问并找出该程序的原因太慢了：即使程序空闲，它也会对 mdb 文件执行数千次读取操作。 通过网络，这当然非常慢：
进程监视器跟踪 http://img217.imageshack。 us/img217/1456/screenshothw5.png
真正的问题：
有没有办法监视负责读取活动的查询？ 我可以设置跟踪标志吗？ 挂钩 JET DLL 吗？ 我猜想该程序正在执行一些昂贵的查询，导致 JET 在此过程中读取大量数据。
PS：我已经尝试将 mdb 放在我们公司的文件服务器上，并取得了成功，访问它甚至比本地共享还要慢。 我还尝试更改客户端上的锁定机制（机会锁定），但没有成功。
我想知道发生了什么，并需要为我们客户的开发人员提供一些确凿的事实和建议，以帮助他/她更快地编写程序。
The problem:
We use a program written by our biggest customer to receive orders, book tranports and do other order-related stuff. We have no other chance but to use the program and the customer is very unsupportive when it comes to problems with their program. We just have to live with the program.
Now this program is most of the time extremely slow when using it with two or more user so I tried to look behind the curtain and find the source of the problem.
Some points about the program I found out so far:
- It's written in VB 6.0
- It uses a password-protected Access-DB (Access 2000 MDB) that is located a folder on one user's machine.
- That folder is shared over the network and used by all other users.
- It uses the msjet40.dll version 4.00.9704 to communicate with access. I guess it's ADO?
I also used Process Monitor to monitor file access and found out why the program is so slow: it is doing thousands of read operations on the mdb-file, even when the program is idle. Over the network this is of course tremendously slow:
Process Monitor Trace http://img217.imageshack.us/img217/1456/screenshothw5.png
The real question:
Is there any way to monitor the queries that are responsible for the read activity? Is there a trace flag I can set? Hooking the JET DLL's? I guess the program is doing some expensive queries that are causing JET to read lots of data in the process.
PS: I already tried to put the mdb on our company's file server with the success that accessing it was even slower than over the local share. I also tried changing the locking mechanisms (opportunistic locking) on the client with no success.
I want to know what's going on and need some hard facts and suggestions for our customer's developer to help him/her make the programm faster.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
为了让你的手准确地了解 Access 在幕后以查询方式执行的操作，有一个名为 JETSHOWPLAN 的未记录功能 - 当在注册表中打开时，它会创建一个
showplan.out文本文件。 详细信息在这篇 TechRepublic 文章 备用，总结如下：
对于追踪噩梦问题来说，它是无与伦比的 - 这是你在昂贵的大型工业数据库中得到的那种东西 - 这个功能很酷 - 它很可爱而且蓬松 - 这是我的朋友......;-)
To get your grubby hands on exactly what Access is doing query-wise behind the scenes there's an undocumented feature called JETSHOWPLAN - when switched on in the registry it creates a
showplan.outtext file. The details are inthis TechRepublic article alternate, summarized here:
For tracking down nightmare problems it's unbeatable - it's the sort of thing you get on your big expensive industrial databases - this feature is cool - it's lovely and fluffy - it's my friend… ;-)
你不能在网络上放置一个数据包嗅探器（如 Wireshark）来观察一个用户和主机之间的流量吗？
Could you not throw a packet sniffer (like Wireshark) on the network and watch the traffic between one user and the host machine?
如果它使用 ODBC 连接，您可以为其启用日志记录。
If it uses an ODBC connection you can enable logging for that.
第一个问题：您有 MS Access 2000 或更高版本的副本吗？
如果是这样：
当您说 MDB 受“密码保护”时，您的意思是当您尝试使用 MS Access 打开它时，您只会提示输入密码，还是会提示您输入用户名和密码？ （或者给您一条错误消息，指出“您没有使用 foo.mdb 对象所需的权限。”？）
如果是后者（用户级安全性），请查找相应的 .MDW 文件与 MDB 一起。 如果找到的话，这就是“工作组信息文件”，用作打开 MDB 的“密钥”。 尝试创建一个带有如下目标的桌面快捷方式：
然后，MS Access 应该提示您输入用户名和密码（希望）与 VB6 应用程序要求您输入的用户名和密码相同。 这至少允许您打开 MDB 文件并查看表结构以查看是否存在任何明显的设计缺陷。
除此之外，据我所知，Eduardo 是正确的，您非常需要能够在开发人员的源代码上运行调试器，以准确找出实时查询正在做什么......
First question: Do you have a copy of MS Access 2000 or better?
If so:
When you say the MDB is "password protected", do you mean that when you try to open it using MS Access you get a prompt for a password only, or does it prompt you for a user name and password? (Or give you an error message that says, "You do not have the necessary permissions to use the foo.mdb object."?)
If it's the latter, (user-level security), look for a corresponding .MDW file that goes along with the MDB. If you find it, this is the "workgroup information file" that is used as a "key" for opening the MDB. Try making a desktop shortcut with a target like:
MS Access should then prompt you for your user name and password which is (hopefully) the same as what the VB6 app asks you for. This would at least allow you to open the MDB file and look at the table structure to see if there are any obvious design flaws.
Beyond that, as far as I know, Eduardo is correct that you pretty much need to be able to run a debugger on the developer's source code to find out exactly what the real-time queries are doing...
如果没有开发人员的帮助，这是不可能的。 对不起。
It is not possible without the help of the developers. Sorry.