Case Study: FileMaker database suddenly slow on startup, then speeds up with use
TL;DR: A corrupt record can cause sporadic performance problems in FileMaker databases, especially when accessing them remotely.
I encountered an interesting error recently. A client database on which I am working remotely suddenly took much longer to open than it used to – increasing from about 20 seconds to fully open to about 4 minutes. The database had a fairly lengthy start-up script, but nothing in it was terribly suspect… the entire system seemed to have suddenly slowed down from one day to the next.
Even stranger, I noticed that as I used it, the performance would improve again to what I was used to, until the next time I reopened it, when the whole cycle would repeat. I had never seen anything like that before. I talked to the client about perhaps troubleshooting their VPN or upgrading their server’s drives to SSDs.
After a few days of this, I noticed a broken value relationship in the file. The primary key in this relationship was a constant – a calculated field with the calculation definition set simply to “1”.
Checking in FileMaker Advanced’s Data View, I saw that this field was not returning a value:
Based on past experience, this led me to suspect internal corruption in the record. To test this, I simply moved to the second record in the found set, and sure enough, the calculation was returning “1” as expected:
Scrolling through a few more records confirmed that only the first record in the database was corrupt.
After backing up the file, I deleted the first record… and suddenly, performance improved to what it had previously been. I closed down the database and reopened it, and the whole thing opened in under 20 seconds.
So I realized, what had happened was that the first record in the database had become corrupt, and whenever this record was active, performance slowed down as the database repeatedly tried to parse whatever was wrong with it. Then, as I used the file I moved around it, and once the corrupt first record was no longer the active record, performance picked back up — creating the impression that performance was improving as I used the database. Next time I opened the database, it reopened back on the first record, so performance was slow until I moved off it again.
As a precaution I took down the database, ran a recover, and imported the data into a known-good backup to use going forward. Performance was restored to its previous best.
So, the moral of the story is: if you’re getting poor performance with your FileMaker database, especially opening it remotely, and very especially if it’s sudden or inconsistent, check and see if there’s any corruption in the file. Run a test Recover on it and see.
