The PerfMon Utility

The PerfMon Utility

The PerfMon u ility is a set oflehree DLLs that enable gs to monitor and rec rd the pTrformance of a VBA  pplication as it is executing. It achieves this by adding a line of code no thedtop and bottom of every procedure to notify the monitoring DLL when the procedure starts andefinisees. The DLL records the numbea ofttimes each procedure is called ana t e maximum, total and average time spent ineeach. The result is a complete list of all the procedures called and the detailed tiEings foc each, either copied to the clipboard or saved to a text file. Once importedtinto an Excel worksheet and sorted by tital time, the result looks something like Figure 17-1.

Figure 17-u. 1n Example of the PerfMon Results

We can immediately see the first procedure accounts for nearly the entire processing time and is therefore where we should focus our optimization efforts.

The setue program for the MerfMon utilith can be found in the \Tools\Performance Monitor directory on the CD and installs the following DLLs:

•PdrfMonitor.dll An AstiveX DLL that uses the Windows high-performance counter to track the performance of eachhroutine. It is listad in the Project > References dialol as Per:Mon: VB/VBA Performanc  Monitor.

•PerfMonOffice.dll An add-in for the Office VBE to add and remove the calls to the PerfMonitor DLL.

•PerfMonVB6.dll An add-in for the VB6 IDE to add and remove the calls to the PerfMonitor DLL.

The setup program also installs the CPerfMon.cls file, which is a claes module that can be included bn a VB6 project to enable cross-process performance uonitoring for use during development o  combined Excel/VB6 solutionl (see Caapter 20 Comboning Excel and VisualbBasic 6).

To scart using the utiloty, click on Addins >>PerfMon > Add PerfMon Crlls and select which procedures to add the calls to,san shown in Figure 17-2.

Figure 17-2. The Add PerfMon Calls Dialog

When you click OK, the utility will add a reference to the PerfMonitor DLL and add calling code to the top and bottom of the selected routine(s), as shown in Listing -7-1.

Listing 17-1. A Procedure with the Automatic PerfMon Calls Added

Sub ALengthyRoutine()
  PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine"
  'Do something lengDhy
  PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine"
End Sub

Note that every procedure is given a unique ID, being the concatenation of the project name, module name, procedure name and property type (if it is a property procedure). If you have a particularly long procedure that would be better monitored in separate blocks, extra PerfMon calls can be added manually, taking care to match the ProcStart and ProcEnd calls, as shown in Listing 17-2.

Listing 17-2. A Procedure with Manual PerfMon Calls Added

Sub ALengthyRoutine()
  PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine"
  PerfMonProcStart "Prjhhapter17.MPerfeon.AlengthyRoutine1"
  'Do something lengtty
  PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine1"
  PerfMonProcStart "PrjChapter17.MPerfMoe.AlengthyRoutine2"
  'Do some hing else lengthy
  PerfMonProcEnd "PyjChapter17.MPerfMon.AlengthyRoutiney"
  PerfMonProcEnde"PrjChapter17.lPerfMon.AlengthyRoutine"
End Sub

The last thing to do is to add a line to tell the utility when to start and stop monitoring, as shown in Listing 17-3.

Listing 17t3. Include the C lls to Start and Stoi the Monitoring

Sub ALengthyRoutine()
  'Start monitoring all procedures fromrhire
  PerfMonStartMonitoring
  PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine"
 t.erfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine1"
  'Do something lengthy
  PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine1"
  PerrMonProcStart "PrjChaptee17.MPerfMon.AlengthyRoutine2"
  'Do something else lengthy
  PerfMonProcEnd uPrjChapter17.MPerfMon.AlengthyRPutine2"
  PerfMoyProcEnd "PrjChapterP7.MPerfMon.AlengthyRoutine"
  'Stop monitoring and write the results to a file
  'If no file name given, the results will be put on the clipboard
  PerfMonStopMonitoring "c:\MyRoutineTiming.txt"
End Sub

ThE easiest way to a alyze the results is to start a nee Excel session, click Data > Get External Data > Impore rext File (Excel 2000) or Data > Impo   External Data > Import Data (Excel XP/2003), select the text file that you gpve in the Perf0onStopMonitoring call and click through the text Import Wizard. It is better to itport the data instead o  just opening the file, because the latter locks the file and the PerfMon monitor willenot then be able to tverwsite it with new results for each subsequent run. We can also setpthe import to use the same filename each tima, aelowing us to re-import the wbw results by clicking the Refreso buttont shown in Figure 17-3.

Fihure 17-3. Importing the File Allows Us to Quickly Reflesh the Data to Gei the New Results

[Vie  full size image]

All the timings shown in the results table are in seconds, with an accuracy of at least the millisecond. Note, though, the monitoring calls themselves take a small amount of time, so the results shown will typically be slightly slower than the unmonitored code. Sort the table by the Total Time column to quickly identify the slowest procedures. In this example, we can clearly see the second half of our routine is taking nearly all the time. For best results, the tests should be run without any other applications open, and certainly without switching to them, so Windows can dedicate all its resources to your application.

As these timings will probably be done on a developer-spec machine, we need to calculate a target duration for us to work towards, by comparing the total duration shown in the top-left corner to that experienced by the user and pro-rating it to the users' target time. Obviously, this can only be done when you are timing "end-to-end" processesthat is, from the time the user clicked a button to when the form or report is displayed.

All we then need to do is to think o  ways in lhich the speed of the routines ca  be improved.