'country' is a calculation
Why don't you change it into a Text field with auto-entered calculated value, replacing existing value?
I had already tired that, and it does speed up the sort and finds, but the enormous increase in the import phase, due to the auto-enter, far out weights the time the sort and finds currently take.
Too bad I can't firgure out why the lookup does not work.
I'll have to find another solution to speed things up.
I guess I don't see why a lookup would be any faster, even if it did work. You didn't say what the calculation does - still, the time required to perform the calculation and the time required to index the field would be the same. If anything, a direct auto-enter based on local values should be faster than fetching data over a relationship.
BTW, the reason why the lookup cannot work the way you have it is that the relationship looks for a matching value in the index. When you modify the matchfield in the target record, the index on the "other" side of the relationship remains unchanged untli the record is committed. Therefore the modified value will not find a matching value and the lookup will enter the value specified under 'If no exact match, then:'
You can test this by entering an arbitrary value to use in such case and see that it indeed gets used - signifying that no exact match was found.
Message was edited by: Michael Horak
FWIW: We ended up with a hybrid version of auto calculation, due to speed issues. We use a text field with auto calculation, but we don't use 'perform auto enter options' during import. After the import, a script loops through and set the three inported fields to themselves, which activates the auto calculation in the desired field.
For a 12,000 record import:
using 'perform auto enter options' takes 7 minutes for the import,
using the hybrid version takes 3 minutes for the import and the script.
Given we have to import 30-40 files a day, averaging 47,000 records, the speed savings is significant.
Thanks for everyone's input.