First some questions:
Which version of FM? 11 or 12
Is your solution running from a Server or Locally?
Presuming that your searching is done via a lengthy looping script, then I'd expect that your biggest bang for your buck will come from installing the fastest SSD drive that you can afford. From my reading, it seems that the SSD chips installed on a PCI card (eg Accelsior card from OWC) currently provide the fastest I/O, thereby leading you towards a MacPro even thouugh the current configurations are long overdue for a design revamp.
That's merely my 2c, you will likely get better advice from developers who are more knowledgeable on the way FMP uses memory and the threading of processes across multiple cores.
This year I'll be using FM 12 Advanced, running on my current (late 2008) MacBook Pro 17". It's only a 2.5ghz Core 2 Duo.
The solution will be running locally, as my MacMini Server is only a 2.0ghz i7. I suppose if I rewrote some of the scripts, I could have them kick off on the Server and run overnight. And in the meantime, I could have those run overnight on my MacBook Pro.
As for the script strategy, I suppose I could rethink things and come up with a different approach. I was going to change things this year anyway, using all the data from all the addresses as a validation of streets, towns and zip codes. We normally only pull out 10% of the addresses based on specific criteria, so the idea of all the data crunching was to make sure more of those addresses are correct. It costs $1 for every mailing that comes back, so every error avoided is $1 saved.
And I agree, I need more input from other developers who know how FMP uses memory and multiple cores. I want a new iMac because I want a new iMac, even if it is refurbished. But if the speed makes it possible to save time on this job, my customer benefits because I don't cost him as much. And I get a new iMac out of it, in addition to the profit I make as a result.
- 3.4GHz is only 9% faster than 3.1GHz, so I doubt that's a good return for your money
- FM12 will work faster with a larger RAM cache, up to a point - a good rule of thumb is that if your RAM cache is larger than your database file, additional RAM won't help.
- SSDs are a major major imporovement. Run, don't walk to buy one of these! Don't buy bargain basement, either.
True story: when I upgraded my development laptop from 2.2 GHz Core 2 Duo to a 2.2GHz Core i7, I thought that some of my filemaker scripts had broken. Turns out they were just running 5x as fast so I didn't even see the window flash.
As for your solution: the answer is probably going to depend heavily on how you optimize your calcuations and scripts - it's easy to come up with a simple but slow script that is easily 100x slower than using another technique.
Bottom line : I'd say you can probably get a 2x to 5x speedup with hardware (Core i7 + SSD + RAM). You can also potentially get a 10x to 100x speedup with script/calc/relationship changes.
Very good suggestions and insights.
1. I agree that 9% faster might not be worth the money, especially if that money is spent on a fast SSD.
2. How do I determine how much RAM cache I am using? The database is 9gb and the tables in question have several million records. But if I extracted the things I want to use for this validation process, I wouldn't need to push around so many bits. And I would only need one instance of each unique street, prefix, suffix, town, state, zip code. That might run a few hours but I could do it overnight. And now my Server starts to make sense again. It's not doing much else overnight.
3. Is the one that Apple installs as fast as ones from OWC? I'm uneasy about buying a new iMac and swapping out the DVD drive for an SSD. What spec tells me how fast it runs?
4. Sounds like any i7 with an SSD and a lot of RAM is the way to go. Maybe I need to wait and see if there is a new iMac 27" next week and order a new one. This job will pay for it in short order.
Thanks for the question that made me realize I could extract the data I want and process that instead. It'll be much easier that way.
- FM Pro or FM Server? In FM Pro 11 it's in Filemaker Pro/Preferences.../Memory. I'm not using FM12 so can't help there.
- Apple used to ship rather slow SSDs but I think the ones they ship now are better. For database stuff, the main thing you want is super fast IOPS and/or 4k block random write speed -- sequential read and write speed is not that critical. I don't know what you'd get in a refubished iMac.
- SSDs + databases = happy times!
Overall, I would probably suggest this: get the new hardware, and see how fast it is. It may very well be "fast enough". If it's not, then spend some time optimizing your scripts, calculations, relationships, and perhaps extracting a smaller set of data to work on.
I have not done extensive testing, at least not recently and especially not in FM12, but in 11 I have done some whilst trying to extract as much performance as possible out of my machine. Anyway, here's my personal 2c.
1. CPU speed does make a difference and can be considerably noticeable when comparing 2.xghz to 3.xghz etc.
Number of cores makes little difference, filemaker does not use muliple cores, although having multiple cores will help keep the OS and other processes from interferring.
2. Ram will only help up until a certain point, FM is 32bit (unless I missed something) so can only address 4gb. Adding excessive amounts will not help at all.
I went from 4 to 16gb and filemaker never battered an eyelid) but the ram upgrade wasn't for FileMakers benefit.
3. SSD's well, I am with everybody else here.. even if it makes little to no difference in filemaker, it will system wide and is definitely a must these days.
I performed a lot of imports, exports and processing tests about 2 years ago now though between 720rpm drives and my intel ssd. There was a performance gain, but not much of one, perhaps 5% and thats being generous, but of course in my test cases.. every solution is different.
As mdiehr says.
"Bottom line : I'd say you can probably get a 2x to 5x speedup with hardware (Core i7 + SSD + RAM). You can also potentially get a 10x to 100x speedup with script/calc/relationship changes. "
The most gains you will ever get out of your database will come from the time and effort you put into optimising the solution files themselves.