VBA Performance Tips for Access (1 Viewer)

[Rx_]

An F.Y.I. only. Trying to speed up some parts of my application and came across this interesting article. As a person that loves "metrics", the process used to determine the results was itself very interesting.

When using something once, it might not make that much difference. However, some of these are mentioned in this general forum. This may or may not help people understand things a little better.

Source and methods used to create the determinations results are at:
http://msdn.microsoft.com/en-us/library/Aa188211

[B]Test[/B]         [B]Optimization Ratio[/B] of Elapsed Times (Smaller Is Better) 
1 Use timeGetTime() rather than Timer 25% 
2 Cache object references 40% 
3 Use Len() to test for zero-length strings 60% 
4 Use vbNullString instead of "" to initialize 35% 
5 Be careful with string concatenation 15% 
6 Use Mid$ statement rather than concatenation 55% 
7 Use isCharAlphaNumeric() instead of ASCII values 55% 
8 Use StrComp to compare short strings 45% 
9 Use Like with wildcards 35% 
10 Use "$" string functions when possible 60% 
11 Use Integers instead of variants 50% 
11 Use Integers instead of variants 50% 
12 Use Integer division (\) whenever possible 70% 
13 Use logical assignments when possible 65% 
14 Use Not to toggle between True and False 45% 
15 Don't use Byte variables for speed 80% 
16 Use For . . . Next rather than Do . . . Loop 50% 
17 Be careful with IIf() 50% 
18 Use If . . . Then rather than IIf 30% 
19 Don't call DoEvents each time you loop 10% 
20 Put the most likely candidate first in Select Case 15% 
21 In arrays, For . . . Next faster than For Each . . . Next 75% 
22 In collections, For Each . . . Next faster than For . . . Next 2% 
23 Set a collection to New collection to clear it 10% 
24 Use early binding 10%

Note: The effectiveness of each of the optimizations depends on many factors, including the actual code in use at the time, the relative speed of your hard disk versus the processor in your computer, and other programs currently using Windows' memory and resources. Therefore, a word of warning: take any suggestions about optimizations with a grain of salt. Try them out in your own applications before swearing by them.

 

[Rx_]

I received some great advice last week about speeding up a query for Access 2007 with a Access back end. But, it just seemed that my Snapshot should have been quicker than a Dynaset. The reference above also reminded me of when to use a snapshot.

For snapshot-based queries, Jet must run the query to completion and extract all the query's columns into the snapshot. When the query contains many columns, it's likely that Jet won't be able to fit the entire snapshot into memory, requiring Jet to overflow the result set to disk, substantially slowing the query. Since Jet reads only the key values of keysets into memory, the same keyset-based query might fit entirely in memory, resulting in a significant performance boost. On the other hand, queries with a small number of columns and rows will likely execute more quickly as snapshots.