Deletions & Imports

[Gambler]

In my application (it's pretty simple -- not many fields), I am importing ~ 50,000 records (about 8 fields). The import works, but it's very slow. takes 10+ minutes on a pretty fast XP workstation.

After I work with the records, I have to delete them. Deleting is almost as slow. I would think that deleting 50,000 records should be done in less than 3 seconds. It takes upwards of 90 seconds. I'm deleting all records in the table, but given how slow this works, I'm wondering if each record is being deleted one at a time rather than deleting in bulk??

This concerns me because when I move this app to a server for multi-user, all I can see is a slow dog.....

In my previous developer life, there were two ways to delete records in bulk

For table A, delete records (this deletes one at a time) or

Delete records in table A (this script is much faster as no individual record selection is done.

Does FileMaker also have "another, faster" way to delete records??

Thanks

Gambler

[Lee Smith]

3 minutes for 50,000 records seems reasonable to me. I have seen it take longer for far less (200 or 300 sometimes). Are you not seeing a message that says FileMaker is reclaiming unused data blocks?

Lee

[Gambler]

"3 minutes seems reasonable"

WOW! that stuns me. If I were doing a selected deletion of records (say every 3rd record), how long would that take (go out for dinner & come back in the morning)?

Does FM not work well with large databases containing large record sets,say 500,000 or 1,000,000+ records??? One would think FM would handle bulk deletions faster than that.

My import of 40,000 records takes > 10 minutes. Do you feel that is also reasonable???

But I guess this is just one of those FM quirks (all applications have them) I'll have to adjust to as I learn FM. FM may not be the fastest horse in the stable, but it generally does finish the race.

[Vaughan]

You must have some relationships set up to delete related records. If so, FMP has to check every record for related records and identify them -- this takes a significant amount of time.

To speed up the process you can "manually" delete the related records before deleting the master records. The GTRR script step in FMP 7/8 makes this much easier to do than in earlier versions.

Delete the related records before the master record: start at the end of the chain.

[Reed]

I have one table with ~400,000 records and I just tested a delete all operation on a 1GHz G4 with 8v2, and it took about 6-7 minutes.

I don't have any cascading deletes set up with this table, but there are a few (6) indexed fields which slow down the deletion a little bit.

[Oldfogey]

I've been deleting small numbers - 4,000 out of 8,000 - and it's slow as a wet week.

Hadn't thought about cascading deletes slowing it down, because these are all records without children. But, obviously FMP has to check this.

Deleting records from the bottom up isn't going to solve this problem. I wonder if you could delete from the bottom up and then break the relationship while deleting? Just a thought.

On slow import, I've done a little playing with similar numbers to the above and my impression is that calculation fields slow it down enormously.

Edited March 30, 2006 by Guest

[Gambler]

Just ran a test in FM versus Access versus Alpha Five versus DataEase on deleting 250,000 records in a single table (no child or related tables).

FM was dead last. FM took 7.2 minutes.

All others did in less than 90 seconds.

Access ~ 90 seconds

Alpha Five ~ 68 seconds

DataEase ~ 45 seconds

I thought I was doing something wrong in FM, but after reading the posts, it looks like slow deletes & imports is just how FM works.

Gambler