Save Records Sorted; Problems / New FM Feature to be included

[mejohn]

Sorting Millions of records takes many DAYS in FM 8 (if it does not crash while doing it) and many hours in FM 10. By placing FM 10 in RAMDisk 1.0 (2 GiB) a 75MB file will take at least 2 hours when sorted on 2 fields. (I am ever so happy I found RAMDisk for OS 10; compliments to the maker).

Whatever I have tried - when saving the SORTED file - the sort order is gone when the file is reopened. The sort order reverted to RecordNumber order - which took quite some time, because before being able to Save, it appears FM first has to "un-do" the sort.

I imagine the SAVE AS command is routed to the RECORDNUMBER Table to SAVE the Records in the - according to FM - "correct" order.

Is there any way to get FM to SAVE AS SORTED:

In other words am I doing something wrong. If so what?

If it is not possible to accomplish what I want, would a NEW FEATURE solve the problem?

New feature: SAVE AS SORTED

- Either with limited any user input - user marking the option SAVE AS SORTED;

- With EXTENDED user input by indicating the FIELD TO BE USED.

(I have a field in which I have - after having performed my SORT - imported sequential numbers starting at 1, ending with the last record number in the file).

Tried to Export the Records Sorted as tab delimited file - took several hours. But then ran into erratic FM behavior where not all records where imported (only 51MB out of 84MB) even though the procedure finished - without crashing, hanging or what have you - after some 10+ hours.

My latest endeavor is to EXPORT the Records Sorted as a FileMaker File. It seems to work but is excruciatingly slow: only 10,000,000 records per hour for 8 fields per record. In total more than 85,000,000 records to export.

Overheard: "You told me not to give up and look for the solution. I have done so diligently. But if you know it is out there, by now, I really would like to know where."

[Peter (duksis3)]

It is difficult to imagine why you need to SORT million of records...

For printout, for viewing...? Why you try to save them "as sorted"? For later opening "as sorted"? Tell a bit more

[mejohn]

In my research project the records are observations of occurrences.

By sorting the occurrences and especially saving the Sort, further analysis (statistical and graph) becomes less cumbersome and time consuming. Hence the need to save the sorted records.

No printing necessary.

[JesseSFR]

If you dataset is static and not growing then you might want to just sort your records and then create a script that will will loop through the records in order and recreate them in a new table. This would then leave you with your same dataset in the correct order. You also might want to create a flag on the records to mark the ones you have completed so that if you have to stop this process you can pick up where you left off.

[comment]

If you sort your file, then do a minor modification (e.g. add a space to some field and remove it) and commit the record, it will remember its sorted state the next time you open it.

Whether it will also remember the sort order of the records, or only the fact that they need to be sorted is another question - I don't know the answer to that.

[JesseSFR]

If this is not a hosted file then it should always remember the state of the records when the file was closed. Is that what you are referring to comment?

[comment]

That is what I was referring to - but it won't happen unless the file is modified.

[JesseSFR]

That's interesting. I didn't realize you had to modify the the file. Thanks.