Microsoft JET SQL Query Logging or "How do I debug my customer's program?"

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.

Solution

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 alternate, summarized here:

The ShowPlan option was added to Jet 3.0, and produces a text file that contains the query's plan. (ShowPlan doesn't support subqueries.) You must enable it by adding a Debug key to the registry like so:
\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug
Under the new Debug key, add a string data type named JETSHOWPLAN (you must use all uppercase letters). Then, add the key value ON to enable the feature. If Access has been running in the background, you must close it and relaunch it for the function to work.

When ShowPlan is enabled, Jet creates a text file named SHOWPLAN.OUT (which might end up in your My Documents folder or the current default folder, depending on the version of Jet you're using) every time Jet compiles a query. You can then view this text file for clues to how Jet is running your queries.

We recommend that you disable this feature by changing the key's value to OFF unless you're specifically using it. Jet appends the plan to an existing file and eventually, the process actually slows things down. Turn on the feature only when you need to review a specific query plan. Open the database, run the query, and then disable the feature.

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