I'm experiencing very slow performance with lookups. I'm using indexed fields with script triggers and inside the script using Set Field with the LookupNext () function. The first time I run the script it takes about 10 seconds. If I change the quantity after that, it is almost instantaneous.
I'm working on a development copy on my machine so everything is local (Mac OS X Lion 2.66 GHz Intel Core 2 Duo). Here is some more information on my setup:
Tables: SalesOrderLine and PriceList
Records: ~ 290k
Relationship defined as:
SalesOrderLine::itemID = PriceList::itemID
SalesOrderLine::qtyOrdered = PriceList::qty
itemID is a text field in both tables.
qtyOrdered and qty are number fields.
All fields are indexed.
I ran through the example in FileMaker's Help Center and created a db with the Items and Shipping Costs. It works fine and fast, but there are only four records. Also, I have a multi-predicate relationship whereas the Help Center example matches just on one field.
I haven't used the next higher/next lower value before, either in the Lookup field definition or LookupNext () function, so I'm not sure if this is normal FMP behavior. My client is moving away from a DOS-like application where everything is keyed and she rarely uses the mouse. I've watched her enter data for sales orders and she is blazing fast. There is no way that an initial 10 second delay will work.