Microsoft JET SQL查询日志记录或“如何调试客户的程序？”

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

它是用VB 6.0编写的

It uses a password-protected Access-DB (Access 2000 MDB) that is located a folder on one user's machine.

它使用受密码保护的Access-DB(Access 2000 MDB),它位于一个用户计算机上的文件夹中。

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?

它使用msjet40.dll版本4.00.9704与访问进行通信。我想这是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来监视文件访问,并找出程序速度如此之慢的原因:它在mdb文件上进行了数千次读取操作,即使程序空闲也是如此。通过网络,这当然非常缓慢:

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.

有没有办法监视负责读取活动的查询?我可以设置跟踪标志吗?挂钩JET DLL的?我想这个程序正在做一些昂贵的查询,导致JET在这个过程中读取大量数据。

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.

PS:我已经尝试将mdb放在我们公司的文件服务器上,成功访问它甚至比本地共享更慢。我也尝试在客户端上更改锁定机制(机会锁定)但没有成功。

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.

我想知道发生了什么,需要一些有关我们客户开发人员的事实和建议,以帮助他/她更快地完成程序。

5 个解决方案

#1

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.out text file. The details are in this TechRepublic article.

为了让你的贪婪掌握正是Access在幕后查询的内容,有一个名为JETSHOWPLAN的未记录的功能 - 当在注册表中打开它时会创建一个showplan.out文本文件。详细信息请参见TechRepublic文章。

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… ;-)

为了追踪噩梦问题,它是无与伦比的 - 这是你在昂贵的大型工业数据库上得到的东西 - 这个功能很酷 - 它很可爱又蓬松 - 这是我的朋友...... ;-)

#2

Could you not throw a packet sniffer (like Wireshark) on the network and watch the traffic between one user and the host machine?

您是否可以在网络上抛出数据包嗅探器(如Wireshark)并观察一个用户与主机之间的流量?

#3

If it uses an ODBC connection you can enable logging for that.

如果它使用ODBC连接,则可以为其启用日志记录。

Start ODBC Data Source Administrator.

启动ODBC数据源管理器。

Select the Tracing tab

选择“跟踪”选项卡

Select the Start Tracing Now button.

选择“立即开始跟踪”按钮。

Select Apply or OK.

选择应用或确定。

Run the app for awhile.

运行应用程序一段时间。

Return to ODBC Administrator.

返回ODBC管理员。

Select the Tracing tab.

选择“跟踪”选项卡。

Select the Stop Tracing Now button.

选择“立即停止跟踪”按钮。

The trace can be viewed in the location that you initially specified in the Log file Path box.

可以在最初在“日志文件路径”框中指定的位置查看跟踪。

#4

First question: Do you have a copy of MS Access 2000 or better?

第一个问题:您是否有MS Access 2000或更好的副本?

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

如果是这样:当你说MDB是“密码保护”时,你的意思是当你尝试使用MS Access打开它时,你会得到一个密码提示,或者是否提示你输入用户名和密码? (或者给出一条错误消息,指出“您没有必要的权限来使用foo.mdb对象。”?)

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:

如果是后者(用户级安全性),请查找与MDB一起使用的相应.MDW文件。如果找到它,这是“工作组信息文件”,用作打开MDB的“密钥”。尝试使用以下目标制作桌面快捷方式:

"Path to MSACCESS.EXE" "Path To foo.mdb" /wrkgrp "Path to foo.mdw"

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.

然后MS Access会提示您输入用户名和密码(希望)与VB6应用程序要求的相同。这至少允许您打开MDB文件并查看表结构以查看是否存在任何明显的设计缺陷。

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

除此之外,据我所知,Eduardo是正确的,您几乎需要能够在开发人员的源代码上运行调试器,以确切了解实时查询正在做什么......

#5

-1

It is not possible without the help of the developers. Sorry.

没有开发人员的帮助是不可能的。抱歉。

#1