Assuming that all 10 fields are stored, the first thing that comes to mind is creating a relationship between the tables with the field pairs as predicates, i.e.
table1::primaryKey = table2::primaryKey
table1::A = table2::A
table2::B = table2::B
GtRR from table2 to table1, matching only
If the matching set = the total set, there is nothing to update; otherwise:
GtRR back to table2 via a relationship that matches only the primary key
import from 1 with update
This will in fact work for 6 of the difference matches and I will use this. A couple of the matches have a custom function converting dates (as there is in fact a file0 that is fully re-populating file1 each night) but I have logged a change for these dates to be converted before we get the data and that change should happen next week.
The other couple are checking for the existence of other related records and a status on the record but I can move these out into constrained finds once I have reduced the sets down based on the method you have outlined above.
I should have posted this years ago!
From you experience is a quicker to do an "replace" or a looping update using set fields?
Thank you for your help.
Foiled - seems something not indexed as it cannot find on related values or go to related values without it hanging. I will keep working on it . Thank you again.
Bummer. Well, the best of luck ...
Good luck. btw, RFC is faster than a script loop.
the replace/find seems to be the sticky bit here. You might try testing this option for speed: script this process, looping (as you just mentioned) through your found set of records, evaluating the unstored field and skipping or omitting that record.
Go to next record - skip exit after last
(see Skip or Omit ... potential pitfall for pitfalls.)
Going through all the records is what takes a long time. So if you can limit the number of records to be compared, the faster it goes. One way to do this is to look at Creation Dates and Modification dates assuming you keep those in your tables. That way you only compare ones where the a modification date or creation date is created than the last sync. Then go back and look to see that anything deleted is removed. It is all just part of doing a good synchronization quickly.
If you really want this to be done optimally and continuously, you might check at 360Works Mirror Sync.
dude, evaluating Modification Date is a great point. Limit the record set.
Thanks again for the replies. No I am afraid that checking mod dates does not help. File1 is imported from another system each night and it does not contain the modification dates from that system. File1 is the master data for a number of fields in File2 but not all... as this table contains a lot of locally collected data also. It appears to be the volume of the data that is causing the headaches. I had read that performing a find with SQL was a lot quicker but I guess if fields are un-indexed then they are slow (sometimes impossible) to find on whether you use a SQL statement or a native find. File1 contains 356,000 recs (which are imported from File0 each night), File2 contains 807,000 records.
Even if I use indexed fields in a relate and find on an indexed related field from File1 to File2 it basically it spends a very long time "processing query" - things are not so bad if I was not using a multi-predicate join but there is no single predicate join that will give 1-1. I could make a relate using combined keys but then I am adding another field that needs to be "calculated" each night as the file0 comes in and it would also need a matching indexed field in file2.
The simple answer is that you need to work with the people sending you the data. Either have them cull the list down based on modification timestamps or send you the data with creation and modification timestamps. If they send a culled down list, make sure they include the ones that need deleted.
SQL in FileMaker rarely performs any faster than a regular FileMaker find. Sometimes it does on things like counts. But don't think SQL in FM makes things go faster. Sometimes it even goes slower. It is a tool to enhance options of gathering data, but it is not a performance tool. Yes, we all thought that it would be a performance tool when it came out, but we discovered otherwise.
You could import into a duplicate table with no indexes because the import will be quick and then look through comparing them to the main database and only updating fields that have changed and most importantly not telling it to set fields that have not changed because that slows things down.
I still recommend you get with the owner of the external data and request they include creation and modification timestamps. That would be the best solution.
Another way to compare records (or multiple fields);
GetContainerAttribute ( list ( firld1 ; field2 ; field3 ; Etc... ) ; "MD5" )
Do this for both records and compare this value to see if they are or are not the same.