Terribly slow import performance?

[xochi]

Anyone else seeing a MASSIVE slowdown in import performance in FM7? I've done some quick benchmarking, and my imports are between 3x and 5x slower than they were in FM6.

Of interest: it appears as if the import starts out quickly and then progressively slows down. For example, the first megabyte of a 5MB file may take a few minutes, but towards the end, it's literally importing at a dozen bytes per second.

This makes FM7 nearly unusable for some tasks, and hard to justify to clients as having "performance improvements"...

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[Reed]

I've noticed this somewhat, but not to the same extent. This was with ODBC import from SQL server.....

[Munik]

Globally, the perfomance of FM 7 is TERRIBLE. I'm using it on a Mac Dual 1GHz and it's 3-5 times slower than FM 6.

[RRRANN]

I'm running FM7 on a PC w/1.8GHz & 1 Gig memmory. Extremely slow importing or dleteing a whole database (1.5 million records, 8 fields each. FM6 would delete all records almost instantly. now it goes through and deletes each one. Takes forever to refresh the database.

Considering reverting to FM6.04

Bob Rann

[vasci]

I am evaluating FM Pro 7 as an upgrade from 5.0 - we need to import TAB Delim files. The Import is hopelessly slow in 7. Imports that took 20-30 seconds in FM Pro 5 are taking 20-30 MINUTES in 7. What a joke! Other parts of 7 ( such as IWP and proper single file relations) are fantastic, so this is a huge disappointment.

Andrew

[vasci]

For peoples interest I found a ( quite bizarre) workaround.

I created a brand new empty database with fields matching the ones to import from the tab delimited file then imported the text-tab file into that file. This was fast.

Then from the main database I then "Imported" records from this .fp7 file. This was fast.

[vasci]

My above solution worked (almost).

I hace coalesced my four separate FM Pro 5 files into one FM Pro 7 database with mulitple tables with relations

- I need to import data into one of the related tables (not the "main" table).

- I created a simple layout that shows data from the table I want ( I think this is badly designed ugly hack in FP7 as the import records dialog will not let you switch the target table)

- I try to import records from my newly created "flat" .fp7 file with the import data.

- I get a message "no fields are defined in the data source"

- If I switch to another layout showing data from the main table

- I try to import records from the same .fp7 file

- No error message and I get the import records dialog - except, of course, none of the fields match as the target is the wrong table

[Klepton]

Yah - super slow imports for me too. Am trying the 'import into a flat file' method as suggested now. After removing calcs, relationships, Value lists from the file, a 10 hour import (of one large file) looks like it will drop to about an hour. Of course - I have no idea how long the flat_file -> final file import will take. if it takes less than 8 hours its worth the extra effort :-)

[FMP Vince]

Did you try the latest FMP 7.0V2 version ?

[xochi]

Have repeated with 7.0v2, no change.

I've done some more tests, and have a LOT more information, as well as a very curious find:

The import performance gets progressively worse, and neither cache flush nor file optimization helps, but quitting and restarting filemaker "fixes" the slowdown. Read below for the nitty-gritty details:

Ok, I've run some follow-up tests, and the plot thickens.

Further tests:

Unless otherwise specified, all tests are using:

FM7 Developer

Mac OS X 10.3.3

PowerBook G4/867 w 1Gig RAM

Importing a 2500-record text file into a table that includes a

calculation that Sums() across related records in the same table.

(1) With Cache set to 128MB/Hourly, I repeatedly imported the records and deleted all records. The time (in seconds) to import and delete records shows a consistent slowing:

Trial 1 2 3 4

Import: 93 153 208 270

Delete: 10 15 20 26

(2) Repeating the test, I inserted a "Flush To Disk" script step between the successive imports, and it did NOT improve performance at all.

(3) Repeating the test, I manually ran the "File Maintenance" command, choosing both "Compact" and "Optimize" options. No change. We still see the progressively worsening performance.

(4) However, if at any point, you quit FM7 and re-open the file, the performance returns to baseline.

(5) Repeating the test, with Cache set to 8MB/Idle (FM's defaults), gives the same results.

Trial 1 2 3 4

Import: 91 153 195 255

Delete: 10 15 20 26

Discussion:

Test #5 surprised me, as in previous testing I believed that the 8MB/Idle cache setting prevented the slowdown. However, in past testing I was using a 10,000 record import, so it could be that the file size matters. If I get a chance I'll repeat that test to see if I missed the slowdown or it is indeed absent in that case.

In any case, I think this has to be a bug. Why should quitting and restarting FileMaker drastically improve performance, when neither flushing the cache nor optimizing the file will? And why in the world should the performance of DELETING records suffer as well?

[rcassidy]

I agree that there must be something going on with FM7 import. The progressive slowdown is likely a memory bug that FM has yet to track down.

Here's a few other data points from my recent experience:

Lookups and autoenter calcs that follow a relationship are devastating to performance - even when all fields are indexed. For example, I need to lookup two fields upon import from one table to another. Import without the lookup takes about 4 minutes (162,000 records, about 16MB). Import with the lookup takes about 4 hours. Now, this is a straight field pull from an *indexed* field. It's no more complex than grabbing the related data for a portal, other than it needs to get written back out to the table.

Next, I did a straight import - no lookup, but ran the lookup as a separate step. Same thing - 4 minutes for the import, 4 hours for the lookup.

Next, I tried doing two imports. The first was the straight import as before - 4 minutes. Then a second import from the tab-delimited file that I populate the related table with, doing 2 field matches on import. So this second import needs to match data against 2 indexed fields and import the two fields that I was previously looking up. This only took 9 minutes.

Now, this suggests a pretty serious bug in FM. There is no way that a field match from a tab delimited file should be faster than a indexed field to indexed field lookup. The latter, in fact, should be substantially faster.

Now, I've found a viable and not too onerous solution to my situation, but I can see plenty of situations that don't lend themselves to this kind of work-around. I have another suspicion that I have yet to test, but will - that lookups across FM7 *files* may be faster than across tables in the same file. I can see a possibility that the performance problem is some kind of odd file locking problem. I'll test that in the next day or two.

[xochi]

rcassidy great detective work. I think we are both seeing the same bug, but you may be closer to narrowing in on it than I am.

Any chance you could put together a simple test database that illustrates the bug? Maybe we could get someone at FileMaker to to pay attention. This has GOT to be a high priority for them, I would hope.

[xochi]

Bump!

And, somewhat related, FMBench 1.2 is out

http://www.lintburger.com/fmbench/

Confirm just how slow FileMaker 7 really is!

[ffarmer]

I just converted a database from MS Access95 (which runs under Mac OS9.2.2, Virtual PC 2.1.3, and Windows 95 -- all old versions) to FileMaker Pro 7.0v2 (running under Mac OS 10.2.8) -- both on the same computer Mac G4/450 with 768 MB RAM. The database is not huge: about 10 tables, the largest has 2500 records, the next largest about 100 records, and most have less than 20 records each. The databases are identical in structure and design. The FP7 version is considerably slower, at displaying reports, than the Access version (even though Access is running under three levels of "systemware")!! Poor show. I expected FP7 to be lightning fast, but it's definitely NOT the case.

Because of other quirks I'm finding in FP7, I'm maintaining both systems "in parallel". FP7 has lots of wrinkles which need correcting before I trust it fully.

[The Shadow]

The progressive slowdown of import is fixed in 7.0v3.