Jump to content

Speed up copying data from related records


This topic is 5749 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5749 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.