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.
It is not possible without the help of the developers. Sorry.
If it uses an ODBC connection you can enable logging for that.
Could you not throw a packet sniffer (like Wireshark) on the network and watch the traffic between one user and the host machine?
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...
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: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… ;-)