Array scanning vs caching performance test

[Cory]

I’ve often wondered how scanning a large array to see if it contains a value compared to caching all the values in a single text variable and doing an “If Contains” so I wrote a test macro last night. The later method is much quicker but one pays a penalty up front creating the cache.

 

 

 

My test macro repeats 100,000 times propagating a text array of the same number of elements with random values. Optionally it appends each of those to a single cache text variable. In the second stage it either repeats 100,000 doing an If Variable Equals for each element or it does a single If Contains against the cache.

 

 

 

For the first stage adding the append command to create the cache increased the time from 3 to 17.4 seconds. For the second stage scanning 100,000 elements for a match took 2 seconds whereas an If Contains against the cache took 1mS. It must be pointed out however that if one expected a hit in the cache one would break out sooner. But depending on the type of data the statistical average would vary so left that out.

 

 

 

In conclusion scanning the array is quicker if one is only doing it a couple of times. But if one is looping thru another array thousands of times scanning each element the upfront penalty quickly pays off. In this gross example in 9 times.