Speeding up exporting and importing

[pjdodd]

In my solution one of my tables has about 300 fields, with a fair few of them being unstored calculations. To export this table takes a few minuntes for only a couple of hundred of records. One the other hand another table has about 100 fields, almost no unstored calc fields and to export this yields about 5 times the speed.

Is exporting speed, field dependent, layout dependent, Filemaker version, or what? I've read the posts and there seem to be no clear answers.

Does anyone have a definitive list of what can be done in Filemaker to obtain maximum speed for exporting records? (Please, no joke replies about buying a supercomputer – I need answers not bad wit).

Thanks

[Ender]

It depends on what the purpose is. If you're exporting to get data into other programs, then there's not much you can do. I suppose you could use lookups to pull in the related data into your export's source table, minimizing the number of unstored calcs. Also make sure your application is updated to the latest rev.

But if the purpose is to populate other FileMaker tables, then there are a number of ways to improve the efficiency (including not moving the records at all).

[pjdodd]

But if the purpose is to populate other FileMaker tables, then there are a number of ways to improve the efficiency (including not moving the records at all).

And these are? (inhales breath and holds...)

[Søren Dyhr]

Easy piecy - relations!!! You need to tell us why the export is required in the first place. A table holding 300 fields isn't the most normalized structure in my ears/eyes.... There are some basic database knowledge ignored here!

--sd

[pjdodd]

The database is exported (a) whenever the client requests a backup (they get jittery, you know) and (: whenever the solution is updated.

This particular table has 312 fields and about 15 relationships coming off it – yes it's spidery alright. It performs a Litho Print Estimate which needs (key word) many (another key word) little (one more key word) calculations. Mathematically there is nothing complex going on beyond elementary addition, multiplication, division and subtraction. In some fields the caluclation also uses some rounding up and logic controls (if field is zero then set current field to 0 - that sort of thing, nothing hideous long like the Case statements you see on here).

There is also indexing on 5 of the fields.

Can a table like this be speeded up do you think?

[Søren Dyhr]

The database is exported (a) whenever the client requests a backup

This is not getting me anywhere nearer, why would such a backup be essential??

--sd

[pjdodd]

And...

When exporting ANY table from my solution, the export speed would start at for example 40-50 records per second (roughly) but by the end it will have dropped to say 8-11 records per second. That's a drop to about 20% of the orignal speed.

(I've tried this on different Macs, G4 and G5 and the same effect is observed).

Does anyone know why this happens?

[Lee Smith]

Have you tried to Save as Copy as [color:blue]Compacted Copy, to see what effect that would have on it?

Lee

[pjdodd]

I've tried the following and there is no noticeable effect on exporting speed:

1) Saving as compacted file.

2) Performing File Maintenance on the database.

3) Recovering the database.

4) Increasing the File Cache settings to 4 times the size of the database.

5) Performing the export after a reboot.

6) Performing the export after the mac has been on for 2 days (an OS X effect: the longer a machine has been running the faster selected operations are, don't ask why cause I dont know why).

7) Performing the export with only Filemaker open.

8) Performing the export with a couple of other applications open.

One observation I did make was that when I opened the database and began exporting straight away (i.e. i didn't add, delete or perform some other operation with the records) there was a distinct 2 to 3 pause before the export progress bar would move to begin.

[Steverino]

I'm also having the problem, but importing the data into a blank/cloned file. I'm using an iMac G5 (with FM Advanced 8.5), and the process is GLACIAL, even though in the import I'm not getting any lookups or setting serial numbers.

Would the whole thing be much faster on an Intel Mac?

What to do?

[pjdodd]

I would check the indexing options on your fields.

I read that having indexes on minimal or higher and eslecting the automatically create option means that Filemaker has to re-create these indexes on import. One of my biggest complaints with FM is that creation of fields ALWAYS has the automatic option checked and left unalone can swell your database un-necessarily and therefore decrease performance.

I have no idea how indexes affect exporting though.