Efficiency

The Performance Analyzer (which you can access from the menu bar by clicking Tools Analyze and then selecting Performance ) is a useful enough tool. It can help you to a large degree in identifying potential problems with database performance, and it is always useful to run it against a poorly performing database application. However, the Performance Analyzer doesn't help you with several other factors that you should consider, one of these being optimization of your VBA code.

If our aim is to achieve maximum efficiency, the key question is, of course, "What constitutes efficiency?" This is a more complex question than it may at first appear. Listed below are four of the most frequently cited benchmarks for evaluating the efficiency of a database application:

• Memory footprint (that is, size )

• Real execution speed

• Apparent speed

• Network traffic

It is nearly always possible to optimize your application with respect to one of these benchmarks, but how do you optimize with respect to them all? The simple answer is that you can't - and you shouldn't try to, because it is not achievable.

One of the key tasks at the start of a development project is to devise a list of coding priorities. Which of the factors listed above are most important for the successful implementation of the application? Which would it be nice to have? And which are irrelevant?

To the four factors listed above, you can add another five:

• Portability

• Robustness

• Scaleability

• Maintainability

• Reusability

And perhaps most important are scheduling factors. Although not optimal, poor performance might be an acceptable price to pay if only the application can be delivered within schedule.

Of course, none of these factors will necessarily help to increase the efficiency of the application - optimizing a piece of code for portability or robustness may well cause the code to run slower or consume more memory than before.

In fact, these various factors can all pull in separate directions. Consider these two bits of code:

 If (bln1 = True And bln2 = True) Or (bln1 = False And bln2 = False) Then    blnResult = False Else    blnResult = True End If 

And:

 blnResult = (bln1 Xor bln2) 

Both of these examples produce the same result. However, the first can take approximately four times as long to execute as the second. If you were optimizing for speed, you would go for the latter. This example shows a clear-cut difference in performance, many others will not be so obvious, but it serves as a good illustration of what can be achieved.

On the other hand, many developers, especially inexperienced ones with no knowledge of bitwise comparisons, would find the first example easier to follow. If you were optimizing for maintainability (and thus easier to read code), you would probably choose the first one ( especially given that, on a typical machine, both examples execute in little more than a thousandth of a second). If you chose the second one, you would almost certainly want to add a comment to explain the code.

This chapter, then, is not going to tell you the optimal way to write your code. That will depend on the coding priorities that you determine for your application. What this chapter will do is to highlight the impact, in terms of the four most frequently cited coding priorities, of various coding practices.

All of the code that is used in this chapter can be found in the Performance.mdb database that accompanies this book.