The Marvelous Optimization Formula
Performance is one of the hottest topics for most FileMaker developers. Now I am going to give you some very interesting and useful information about my last experiences with improving speed of FileMaker solutions.
If you want to learn how to not only optimize your scripts but also succeed in speeding up the whole solution, enter your name and your best e-mail address below. I will let you know as soon as I have the next video ready.
We all live in a world where everything keeps getting faster. We don’t even manage to adapt to one thing before another one is coming. The most visible effect is that we all are getting used more and more to live like that, work like that, and be like that. When anything becomes a little bit slower than we are used to, we start to be nervous and angry.
I remember the times when I started developing our own information system (called CIS) in 24U. Everything was fine. We had a couple of computers, a couple of users, and our company had much less orders and customers than we have now. But as we were expanding, more and more parts of the system started becoming less responsive. Users started to complain about the speed of our system.
We tried upgrading to a new version of FileMaker Pro and FileMaker Server, and gained about 15% increase of the system’s overall speed. When it became slow again we moved our FileMaker Server from a Mac mini to an XServe. This led to about 30% speed increase. Next time we upgraded all our client computers to Intel Macs. CIS speed increased by 20 %. These were easy solutions but not very cheap and definitely not as notable as we hoped for.
Then we found that backup can slow some operations down several times. So now we run backups only once an hour. We also deferred some lengthy tasks to run as server-side scripts during night. Curiously, results of these cheap changes were much more notable than the costly hardware upgrades. But after a few months our nightly scripts were taking nearly 8 hours to finish. I saw a big speed issue approaching again.
One time I was even thinking that we might have reached the limits of the FileMaker platform. This was really getting me down.
But I didn’t want to believe that. I told myself that there must be a reason why we can have millions of records in a FileMaker database, that there must be a way how we can work with a lot of data efficiently.
So we started looking closer at how we could optimize our code. Not saying that we were not trying that before. We just decided to put more energy and thinking into it.
And guess what, it helped. We got the idea we were looking for.
Now I am happy I found another way after these rich experiences. Now I know we can speed up a FileMaker solution using the method we discovered and the way we do it is faster and cheaper than anything else we tried before!
Everything is possible when you believe
Now I want to ask you for something…Try to imagine you have a chance to speed up your solution, for example, by 300,000 %, and the costs are minimal. Do you like it?
I found a way how to do it! I found what I call “the marvelous optimization formula.”
If you read my blog “HOnza’s Bits @ 24U” you have probably seen my article about our optimization of the inventory script. Solely by optimizing our algorithm, we sped this script up literally by 300,000 %. We cut the execution time of this single script from 5 hours 11 minutes and 33 seconds to just 6 seconds.
This success brought new hope to our FileMaker development. We started to care more about efficiency of our code. We tried to optimize other scripts and other solutions, and saw great results there as well.
This experience has taught me three important things I want to share with you:
- It is necessary to optimize scripts. If you don’t do that you will waste a lot of CPU power that could be used for much more useful work.
- Perceived speed of a solution is more important than the available feature set. Features make users happy only if they can be used efficiently.
- Everything can be optimized. You just have to believe you can do it.
Yes, you can speed up your FileMaker solutions significantly, solely by optimizing your code. But you have to be aware of some drawbacks.
You can easily spend a lot of time without anyone noticing any speed improvement at all. This is exactly what happened to me with the inventory script I wrote about on my blog.
Fortunately, I have identified these drawbacks and found a way how to deal with them to avoid such failures. There are things you have to do and others you have to avoid if you want your optimizations to cause actual effect. And I am going to reveal my findings to you in the next video.
{ 13 comments… read them below or add one }
Been there, done that…… but well organized thoughts will help remind me and refocus me on speed…..
Sorry I missed you at DevCon this year…. ( I missed DevCon….. )
Terry
Thanks, Terry, the fact that you have gone through similar experience tells me I am focusing on the right thing.
I missed you at DevCon as well, but I hope my videos will compensate that you could not see what we were showing there
I watched your video and I’m quite intrigued. I can’t wait to find out more. I’m in the same boat with a vertical market solution that I’ve been developing for a company for 5 years now. It is so inefficient!! I will be rebuilding from the ground up and I want to know what to avoid when rebuilding it.
Looking forward to the great things that come from you, you’re a great help to the FM community!
Wow, rebuilding from the ground up is a big (and hard) decision. I am sure that implementing the techniques I will be showing in the following videos will definitely help you to keep your solution’s efficiency under control. But you may even also find out how to quickly improve your present solution without having to rewrite it. So stay tuned and don’t give up…
Looking forward to the next video and FM Bench
film very good; looking forward to next video;
You have given hope.
I’ve been slowly implementing this into a mature system that is in desperate need of optimizing.
There are two features that I would like to have, and would like to see if these are already implemented and I didn’t notice, or something for a future release of this great program –
1) In the case of multiple subscripts, the parent_ID only captures the immediate parent’s ID. Is there a way to get the whole ‘family’ if it’s three or more scripts deep? This would be much like the script stack that can be viewed in the script debugger in Pro Advanced.
2) Calculate and record the total run time for a script when the exit/halt event is received. For the same instance_id, subtract the start event from the exit/halt event. I might be able to figure this out by myself (using global variables), but I haven’t been able to wrap my head around it quite yet.
Excellent job by the 24U crew!
Great to hear you’re already using FM Bench Detective and getting some experience!
The features you’re looking for are part of the full FM Bench product we’re going to release in a few days. Stay tuned
Excellent starter, Honza, and thank you for concentrating on the most important aspect of developing databases for ourselves and our clients – it’s not about three pages of features and neat coding and acrobatic scripts. It’s about giving our clients the ability to do more work, more quickly, more safely, more easily. I have just finished a year-long contract at a company with 7GB of databases, 22M records, 2800 TOs, 2400 relationships, 19K fields, 5800 scripts and 330 files. It was slow, overly complicated and painful to support, develop for and maintain.
And the users? Confused.
But, small changes to layouts, small changes to rationalising scripts and ‘process’ brought us big gains in the users’ ‘perception’ of ease of use. And when they thought it was easier to use, they made the effort to use it more. And got more done, more easily – while the developers tinkered away behind the scenes.
I look forward to the next of your videos and to learning about your Marvelous Optimising Formula. Anything which can accomplish the goals will be a good thing.
One of the things I learned was to test everything – and challenge everything. Did we need a new calculation field when we could accomplish the same result with conditionally formatted layout text? No. And by testing on 1M records I was able to prove that the performance hit was much less for the formatting than for the calculation.
Question: have you tested the limits of FileMaker using your new techniques? Have you worked with hundreds of millions of records, or billions? I am going to see what happens when we start to put extra zeroes at the end of our record counts. Currently, I have only worked with files up to 30M records… this month I will start with ten times that amount.
And will use your techniques to help optimise the processes – I will let you know about our results.
Thanks for your praise. I am glad to read about your success making your solution perceived well by the users.
It’s hard to reach the actual limits of the current version of FileMaker products. We have worked with tens of millions of records, and specifically with FM Bench we were challenged to go through millions of records in a loop, when processing the logs generated by FM Bench Detective. The main thing I have learned from this practice is that the more records you are working with the more you have to think and test different approaches to the same problem. Because the more records you have the more is the overall system performance affected by your good and bad decisions. And FM Bench is intended to help you significantly with these decisions. FileMaker can work with billions of records but you have to know what you can do and what you have to avoid.
Hello HOnza,
I have just watched your first two videos, & have to say I’m very keen to see how affective your method is & have a few solutions that i look forward on trying it with.
But I have to say that you need to add a link to download the video’s. There very slow to watch from where i am, & this makes them a very annoying viewing experience. And second, I don’t mean to nit pick but the links & comments when signing-up for membership, lead one to think that “Finding the Bottleneck” is the first video (It seems like the second to me). When “The Marvelous Optimization Formula” I believe is the first & is a much better introduction to selling your self & method, though the second one is better for explaining how simple “24U FM Bench Detective” is to implement into one own solution.
Thanks for your feedback. I am glad that you are finding FM Bench and my videos about it useful.
For the case you didn’t know, the video player has a small HD button that lets you enable or disable HD video. I have just discovered that I had HD enabled by default, so I have just changed it to disabled by default to improve the load speed.
You are right that “The Marvelous Optimization Formula” is the first video and “Finding the Bottleneck” is second. I am not sure what exactly makes you think something else but I will be glad to correct it if you let me know.
I just wrote an article with a FileMaker Script Optimization example where a script was improved from taking 9+ hours down to less than 30 seconds. Thanks for the ideas. http://www.cimbura.com/tech/wordpresstech/index.php/2012/01/29/filemaker-super-script-optimization/