Locating/Debugging Slow Code

[chunter33]

Hey there,

I will soon be taking on a project regarding optimizing a series of Access databases to run with better performance. In Access 97 these databases ran queries and modules with no trouble at all, however, after upgrading to 2007 there's been a huge impact on speed.

I was wondering if there was a conveinent way of locating specifically which code is bogging down the application. Or is trial and error bascially the only way?

For the moment I have a list of things I want to look for and try out from Microsoft's article on the matter (see attachment). But these are some pretty HUGE databases, some with as much as 20 or more modules!

 

[smedvid]

Well...
I have a tblLog where I can insert performance and other metrics to record application actions. My date_stamp field is defaulted to current date/time and I have an Auto ID as Primary Key; so I only need to insert Process Name and other comments.

Only other way I can think of is to run in Debug mode with break points and/or perhaps manual message boxes with date/time... etc...

htwh...

Steve Medvid
IT Consultant & Web Master

 

[chunter33]

Running in debug mode seems to be the easiest way to do things. So long as you have a general idea of where things are running slowly, simply set a break point, run code stop, set another breakpoint continue, determine if the block of code between the two breakpoints is running slowly and repeat. Works wonders!

 

[JonFer]

Add debug print statements at the major steps:

[tt]Debug.Print "Start step xx: " & Format(Now(),"hh:nn:ss")[/tt]

If you are running a lot of queries sequentially, use a function to run them and store the processing time and the number of records affected in a table. Very useful for performance tuning.

 

[oharab]

I've created an access add-in that may be useful for this, it's up at

https://github.com/oharab/log4vba/

It's a rough port of the [

http://logging.apache.org/log4net/

]log4net library and basically it lets you add logging statements throughout your code, but they can be turned off or output to different destinations (console, text file, email, etc) by changing an xml file.
It automatically includes time, so you can use it for tracing through your code.

Let me know if you think it's useful or need a hand setting it up.

Ben


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.