Performance Tips

One negative aspect of the reporting methods proposed in this chapter is the time it takes to generate a report. As mentioned earlier, the primary cause of this time hit is the overhead of cross-process-boundary communication. For example, pushing data from Visual Basic into Word crosses at least two process boundaries, possibly more, depending on how your data is retrieved. This situation is improving with each new release of Office and with the improvements in ActiveX and the COM architecture—and, of course, the lightning performance packed into the latest Pentium Pro computers doesn’t hurt.

Many of us, however, are stuck with earlier versions of Office and less powerful computers. If you fall into this category, you’ll need to take into account the performance issues. Here are a few tips for improving performance:

• Ensure that the foreground and background tasks obtain an equal share of the processor’s time. In Windows NT 3.51 and Windows NT 4, it is possible to do this by setting the multitasking of foreground and background activities to be equally responsive. The option is available in the control panel under system properties.
  
  
• Don’t display the report as it is being created; this will reduce the overhead of displaying each change made to the screen before the instruction is completely processed.
  
  
• Where possible, try to run the code within the reporting tool. Generating the output is normally far slower than retrieving the data. If you’re able to run the output code within the same process, you’ll be able to reduce some of the overhead.
  
  
• Employ intelligent code. For example, if you’re finding field descriptors, it might be quicker to read the cells within a range individually rather than to use the Find command.
  
  
• Place data into the clipboard or into a file to be read into the report tool. Doing this might be faster than populating data line by line.
  
  
• Turn off functionality within the reporting tool (such as the automatic Re-Calc facility in Excel) that could slow down the process when generating a report.