import vs. replace - performance

[happymac]

I have a database that has to perform a replace field contents on hundreds of thousands of records on a weekly basis.  Basically we get a CSV file and import it, then need to match a bunch of data to the existing records in the database.  The problem is that the replace script is incredibly slow ... taking days to perform.

 

I'm wondering if running an import (from one table to another using matching fields) would be faster than the replace function OR are they about the same in terms of performance.

 

i realize there are many factors that affect the speed, but generally speaking is import faster OR roughly the same?

 

thank you!!

[Wim Decorte]

A straight import may be, but I would not expect an "Import matching" to be faster.  Should be easy enough to try out though.

 

A replace on a few hundred thousand of records should not take days, though... can you explain a bit more about the structure of the tables?  Lots of stored calcs?

[Fitch]

If you're using the Replace command on multiple fields per record, it will be more efficient to use a Loop/Set Field/GoToNextRecord script.

 

This is because each record that is touched by Replace has to Open/Lock the record, and then Commit it. Multiple Replace commands mean you will do that multiple times, whereas with a looping script, you open each record once, set all the fields, and then commit. The more fields you're working with, the greater gains you will realize.

 

For fastest performance, use a layout with no fields, in form view, and freeze window before you loop.