FM7 database is slowing down

[pigslow]

I have taken over a database with a new employer. This database has 37 files with the largest file having just over 700,000 records and is still growing. With other databases I have worked with I just archived older records but this solution is different. I need to have all of these records live all of the time. My estimated growth is about 2500 records per week in the largest of the tables.

It seems that when we added the last push of records from about 640k to 700k that is when things really slowed down. One of the scripts that pulls in new records from a MySQL database went from 8 hours to a 14 hour job.

What is causing all of this trouble? Is this too much for filemaker to handle?

[gdurniak]

some suggestions:

1. try saving a copy as "compacted", to clean up the file

2. keep the number of indexed fields to a minimum

3. beware of "lookup" fields. Use auto enter calcs instead

4. try importing in smaller batches

[pigslow]

I have been saving the db as a compact copy every other week.

What is a good practice for determining which fields need to be indexed and which do not.

I will have to check if there are any lookup fields.

My import scripts keep the filemaker database in sync with the MySQL database. I used to run it every day, which kept the numbers down, but the run time started to run beyond the window of where there were no users in my db so I went to a once a week run on the weekends.

I am going to throw some more ram at the DB and see if it helps as well. My server is a dual 2.4 Xeon processor with one GB of ram and 10000 RPM SCSI drives. My filemaker DB is just over 2GB and the MySQL DB is about 8GB in overall size. More ram cant hurt.

Thanks for the suggestions