Formula Fill Timing Issue

[David Thompson]

>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.

It wasn't a dream, but I don't think it was 
documentation either. I think it was the email 
quoted below. By the way, if each of those 
databases had been 10 times as large, ie 270,000 
and 100,000 records respectively, you could 
figure on your test taking 100 times as long. 
That would be 13,500 seconds (3 hours and 45 
minutes) and 38,900 seconds (10 hours and 48 
minutes) respectively.

Dave

Date: Sun, 01 Dec 2002 11:25:58 -0500
From: Mark Terry <terry at tcimet.net>
X-Accept-Language: en-us, en
To: qna at lists.provue.com
Subject: Re: lookup speed
Reply-To: <qna at lists.provue.com>
Sender: <qna at lists.provue.com>
X-Status:
X-Keywords:                

Good call, Dave. The larger database has over 200 
fields, and the keyfield in the slow example was 
4 from the end. Moving it to the number 2 slot 
makes it faster than the "fast" example. Thanks 
to you and Scott for the input. Incidentally, I 
tried reducing, and then eliminating the keyfield 
alpha characters in the slow example and seemed 
to show a marginal improvement, but not nearly 
enough. Still, it makes sense to avoid lengthy 
string prefixes in large db's.

So far, it looks like field position should be 
viewed as the "prime" consideration for lookup( 
speed.

Mark

P.S. If the lookup( functions behave as you've 
described, it seems like they could be tweaked to 
jump to the correct field position more directly, 
doesn't it?

David Thompson wrote:

>I thought of something else that might contribute to the speed of a
>lookupall(. Put your key field as far to the left in the data sheet as
>possible. Panorama fields are variable width and each cell begins with a
>length code telling how long that particular cell is. In order for
>Panorama to find the second cell in any given record, it has to read the
>length code for the first cell, then it needs to read the length code for
>the second cell to find the third, etc. Panorama can jump instantly from
>the beginning of one record to the beginning of the next, but it can't
>jump instantly from the middle of one record to the middle of the next.
>
>Dave
>