Quick way to Replace

[Peter1]

In some situations you may find that Importing records is much quicker than using the Replace function. In one of my solutions I needed to Replace 12 number fields with data from 12 calculation fields in the same table ("Customer" table). The process was incredibly slow so I tried a different method which was much quicker.

I created a new table called "Temporary". This table has number fields with the same name as the relevant fields in the Customer table as well a field called CustomerID. I imported all the relevant records from Customers (the 12 calculations) as well as the CustomerID into the Temporary table. I then imported these back into the Customer table based on the relationship between CustomerID (in Temporary table) and CustomerID (in Customer table). The import was directed to my 12 number fields. No customer records are deleted or added - just the number fields are updated. I then deleted all records in the Temporary table. So I was effectively doing a Replace by means of the double import. (You don't need to set up the relationship in the graph as this is done in the import settings)

The times achieved were:

Note: the 12 calculation fields each were summing values from a separate table with 4000 records (approx. 100 related to each calculation result in Customers)

2500 Customer records took 452 seconds to Replace the data from calculation to number fields.

2500 Customer records took 89 seconds to import into Temporary, import from Temporary to Customers and delete all temporary records.

60 Customer records took 14 seconds to Replace the data from calculation to number fields.

60 Customer records took 3 seconds to import into Temporary, import from Temporary to Customers and delete all temporary records.

In my solution users will regulary run this script on anywhere between 10 and 60 Customer records. 14 seconds is just not an option - it feels like an eternity. 3 seconds is far more acceptable.

Peter