Formula Fill Timing Issue

[Mark Terry]

On Feb 9, 2008, at 11:30 AM, David Thompson wrote:

>> The further the data column is from the key column, and the further
>> either of them are from the first column, the longer the  
>> lookup( time.
>> Since it's so easy to move field positions around in the Design Sheet
>> (Cmd-up or -dn), you might consider making them cols 1&2, at least  
>> for
>> your lookups.
>
> In Ken's case, there were 200,000 records in each
> database, according to information provided in a
> later email. Over the course of the FormulaFill,
> the lookups( would be doing a total of
> 20,000,000,000 comparisons between the key value
> and the key field. (The average lookup will find
> the record it's looking for after looking through
> half the records, and there will be 200,000
> lookups.) That's going to take an inordinately
> long time no matter where the key field is
> located. With databases that size, you want to be
> looking for any alternative to the
> lookup/formulafill combination that you can come
> up with.

At the risk of discovering another documentation dream, I ran a quick  
test (I just had to know. ;)

The lookup db had 27,000 records with 269 fields. I formulafilled a db  
with 10,000 records. In the first test, the key field was the first  
field, the data field was the last. Total elapsed time: 135 seconds.  
Then, I moved the key field to column 50. Total elapsed time: 389  
seconds.

Whew!

M