Lookup Function Performance

Panorama X
1

Does anyone use the lookup function? I managed to trash a field in a database this morning and thought to repair it with a formula fill with a lookup from a backed up version:

lookup(“UFT2”, DATEORD, DATEORD, surname, “not found”, 0)

Well, with 100,000 records, the progress bar just sat there.

So I decided to choose subsets of the file, to LOOKUP. I was hoping to find some ideal number of records to lookup at once, for the fastest result, but what I saw was results all over the map. Here is a list of the number of records chosen, the time it took to perform the formula fill, and the number of records per second being processed. I have arranged them in the order of the size of the selection. Look at the tremendous variation in the number of records that Panorama can process per second:

#records chosen Records per second
seconds to complete

00143 008 018
00161 008 020
00166 007 024
00181 007 026
00315 027 012
00523 004 131
00563 028 020
01191 055 022
02882 098 029
03018 010 302
03032 173 018
03060 090 034
03877 022 176
04152 219 019
04835 038 127
05008 060 083
05049 134 038
05487 189 029
06111 100 061
06574 480 014
07372 510 014
07388 160 046
09261 400 023
12616 669 019

Low of 12, high of 302, what in the world is going on here? It can’t be anything to do with the lookup itself; finding a match in a hundred thousand records takes no more than 17 compares. And Activity Monitor assures me that Panorama was using 101% of the CPU.

Does anyone have any cute suggestions for ways of using Lookup so that it doesn’t take all day?

2

Sure, if you’re using b-tree indexes. Being RAM based, Panorama doesn’t use indexes.

As you’ve discovered, using formulafill with lookup( is not very performant. Fortunately, there is now a much faster way - set up a relation and use a join. With 100,000 records, this will be thousands of times faster (it actually builds a temporary index, then does the join using that).

3

To emphasize what Jim said, I had a similar project to “repair” a field by bringing over records from a second database. The volume was about the same as yours and I used LookUps. It took HOURS and I had to break the database into smaller sections or the lookup loop would stop.

After the deed was done, I tried Jim’s “Join” suggestion. It took SECONDS to accomplish the same thing,