Speed up copying data from related records

[Revuelta]

Perhaps you can help me with a little problem.

I have two related tables with more than six millions records. I want to copy the contents of twenty fields of one table into twenty fields of the other. The reason is that operations that involve related data are very slow for my purposes, since related fields cannot be indexed. So instead of making use of relationships I have decided to copy data from one table into the other to speed it up . And indeed the database works much faster although its size has increased.

The problem is that this task (copying data from 20 fields of 6000000 records) is very time-consuming although I am working this way:

1) During the script the window is frozen.

2) I have chosen "view as form".

3) I have chosen the script step "Replace field contents" instead of looping through the records.

4) I am working on a MacIntel core 2 duo (2,33 MHz) with MacOs 10.4.8. So the system is quite fast.

I have tried to use the lookup function, but the consumed time is exactly the same. I have already tried with indexing selected and unselected in all fields of the database. But there was not much difference.

Could you tell me how to carry out this task in less time? Or is it not possible due to the high number of records and fields? I have the feeling I'm doing something wrong and it should work faster.

I used to carry out such tasks in less time with previous versions of Filemaker (version 6 was much faster!), but since I am using unicode text, I am forced to make use of FM 7 or 8.x.

Thank you in advance for your help.

A.R.

Edited January 2, 2007 by Guest

[Stuart Taylor]

Why not just use the import script step?

You can import from one table in to another in the same file.

Its got to be the fastest method.

[comment]

Indexing 6,000,000 records is going to take some time, whichever method you choose. You could try the following method to eliminate all other factors (I think)

Define an unstored calculation field = a related field. Leave Define Database mode. Re-enter Define Database and change the new calculation field to Text or Number or Date, whatever's appropriate. When you're done, Filemaker will index the field.

However, data duplication is rarely a good solution. Perhaps you should concentrate on those "operations that involve related data" instead, and see if they can't be optimized to use stored data in the related file directly.

[Revuelta]

Define an unstored calculation field = a related field. Leave Define Database mode. Re-enter Define Database and change the new calculation field to Text or Number or Date, whatever's appropriate. When you're done, Filemaker will index the field.

I did as you told, but when I converted the calculation field into text field the content disappeared. Does it have to do with the fact that the formula makes use of a related field? Or did I do something wrong?

Thanks in advance.

Edited January 2, 2007 by Guest

[comment]

No, you are right and I was wrong. I wasn't aware it won't work with an unstored calculation, but it actually makes more sense that way.

My second suggestion still stands, though.

[Fenton]

I would agree with comment, that you should look hard at operations involving these fields, and see if you can do part of them in the related table instead. Finds should be scripted. There is seldom an absolute need to use related data, not in scripted operations. You can go to the related table and do what's needed then come back.

There are various methods to capture and compare the IDs collected in this manner, the latest tool being List(); but it requires 8.5 on all machines; there is also Filter(). The old standby Copy All Records is pretty fast. For Finds there is Constrain Find. There are some Custom Functions which work with lists.

briandunning.com/filemaker-custom-functions/list.php

But, if you must copy the data to the other table, I think a Loop (in Form view) is going to be faster than 20 Replaces, especially on hosted files.

Or, can you capture those 20 fields during data entry and editing, then copy them at that time, using an interface with globals, or perhaps with a free event-triggered plug-in, then run a script? It must be fool-proof however.