Fastest way to make related records?

[xochi]

I'm using FM server 7. I have a table that has 25,000 records in it, and I need to make related records in a join table. The two tables are related by a two-field match (ID and Date). I've come up with several ways to do it, but none of them work well, and I'm looking for hints on how to do it better.

For purposes of discussion, assume the two tables are named "A" and "B". Also, table A has a many to one relationship with table B (i.e. multiple records in A for the same ID/Date combo), but I only want a single related record in table B.

Method 1: Looping set field.

Find all records in A with a child in B. Omit these, leaving only those w/o children. You have to do it this way because you can't find for "=" (blank) related records, which is annoying but another discussion. Loop through all records in table A, setting the related field in table B.

Pros works, and by design won't ever make multiple children in table B. Clean, as it doesn't require any calculated fields in either table.

Cons: Slow. Very slow. Terribly slow! On a 100 base-T network, it runs at about 8 records per second, even when I freeze the window. There is also no progress bar to give an indication whether it's 1% done or 99% done.

Method 2: Import

Find for non-matching records as above. Import into Table B.

Pros: Fast -- 10 x as fast as method 1. Progress bar shows how long until it's done.

Cons: Doesn't handle the many-to-one relationship. It creates multiple child records in table B.

Method 3: Import unique values only.

Same as method 2, except we have a calculated field in table A Unique = If(Self::SerialNumber = SerialNumber,1,0), and we limit the records to only those unique ones before the import.

Pros: The import is fast.

Cons: Requires another relation, and the calculation of the Unique field is slow.

Method 4: Import / Update

Same as method 2, except instead of Import / Add new records, we Import / Update records in found set, with "Add remaining records" selected.

Pros: none.

Cons: doesn't work. Import/Update will happily import multiple child records into table B.

Method 5: Replace Field Contents

Find non-matches in Table A as per method 1.

Replace Field Contents to table B with a calc of ID.

Pros: Elegant, has a progress dialog.

Cons: Doesn't work. -- Replace Field Contents won't automatically create related records in table B.

So, in summary, an operation which should be fairly straight forward turns into a bit of a nightmare. You can do it the "right" way which is godawful slow and has no progress bar, or you can do it the hack way which requires you to add extra fields and relations to one of the tables.

Ideas?

[fmsavey]

Try this. In the defined relationship window double click on the relationship between table A and B. Under table B check the box that says "Allow creation of records in this table via this relationship". Now write a script that goes like this.

Goto Table A

Show All Records

Goto record 1

Loop

Set Field (table B::anyfield; table A::anyfield )

Goto Next Record, Exit After Last

End Loop

This will create one related record in table B where there is none and update anyfield in table B where a record already exists. This takes only seconds to do for 25,000 records. Just make sure there is a value in anyfield otherwise the record will not be created. Anyfield in table A can be a global field.

[xochi]

Thanks for the suggestion -- I think yours is the same as my "Method 2" above.

I determined why the loop is so slow on my system -- it turns out my Table B has a bunch of complex auto-enter calcs (that sum across related tables). So a simple "Set field" actually is triggering a bunch of sub-calculations when the record is created (at a time when I don't want the fields updated).

In this case, the import ended up being much faster, because you can de-select the "Allow auto-enter options etc." checkbox.

So, in summary, I think I may have unfairly maligned FileMaker Server 7 -- it's not as slow as I was claiming.

Don't, however, ask me about the speed of deleting records... grr.

[Søren Dyhr]

Ah I found where you explains the need for both imports and swift deletions!!!

If the need to create and delete records in a join table becomes that urgent, have we found that using a combination of CF's and repeating fields, is much faster than generating real records... Experiment for your self try to make a repeating field key in one of the tables and relate it to the ID's in the other table.

Especially does the Get ( CalculationRepetitionNumber ) com in handy if the joins are made between spans of time, in that case do we have repeaters locking to repeaters.

--sd