I am having an issue with a database running in FileMaker go 12. The database contains calculated fields and is running very slow.
Has anyone else experienced this problem ?
Indeed. Much has been posted about a variety of performance issues in FMP 12, several of which have been addressed in bug fix updates. I haven't yet tested v3 for performance enhancements for calculated fields, but the notes released with v3 did not say anything about performance and calculated fields.
There are many workarounds for the problem: auto-enter calcs, script triggers, scripted updates of indexible fields to sync with unindexible calculated fields, etc. But there can be issues with any of these methods, the biggest being that many of us have systems we've been building for years that have many calculated fields that used to perform at acceptable levels. Changing these fields to one of the previously mentioned strategies could involve enormous developer time and huge sums of client money.
My biggest client's database could not be converted and used in FMP 12 because user tests showed the slowdown in the calculated fields engine had a significant negative impact on user production. My client has faithfully paid thousands of dollars to FileMaker every year for "maintenance" upgrades so he could always have latest and greatest. I won't repeat what my client said when he found out this upgrade is not usable by him without his paying me tens of thousands of dollars to rework calculation fields that performed fine from FMP 3 through FMP 11.
Ann Arbor, MI
Can you say a little more about your database?
*Tooltips don't render in iOS, but I think they calculations are still process, though I may be wrong.
This is a very common problem with unstored calculations. Whenever possible, replace unstored calculations with stored data fields which are either updated via auto-entered calculated values of by scripting. This can reduce performance time by as much as 98% in some cases I have converted. In other words, a process that took 50 seconds now takes 1 second. YMMV, but the concept holds true pretty much across the board, and is especially critical in WAN environments where the data required by the calculation must be cached across the network before the calc can be resolved.
Also, avoid list views with unstored calc results, as these require caching for larger numbers of records before the screen can refresh.
Where are you hosting your database?
Run a traceroute to test for latency.
I am based in Dublin. We hosted our database with an American company. After making changes, such as those suggested above, to calculations and stripping down layouts to their minimum, the database was no faster. My helpful hosting company, oditech, trouble shot with me and got me to run a traceroute. I could see that there was huge latency on several of the IP addresses between Dublin and America. I queried it with my broadband provider - I have 100mb per second. They got me to test the latency with other broadband providers. . It was equally bad. So we ran a traceroute on a UK based hosting company - much faster. We switched to them yesterday on a months free trial. It's all going much faster.
Here are the responses to your questions,
First some background on the database
The database is for collecting statistical information for a collegiate bowling team. We are collecting pin state (which ball knocked down with or standing), mark information, pocket hits, etc..
1) The database is local to the iPad
2) The layout that is slow is the main layout for collecting the data
3) The layout contains portals for current game statistics and a portal showing the data from each frame of the game (12 seperate portals - one for each frame and 2 additional for the 10th frame extra shots). The current game statistics table is all unstored calculations.
4) The main scoring table current contains 25 fields. The statistics table contains 21 fields (17 are calculated)
5) There is conditional formatting in the layout. The only calculated fields are in the game stats portal
6) Calculated fields are all unstored
7) The calculations consist of Counts, Sums and ValueCounts.
I have noticed that if I remove the relationship to the main table for the statistics table it alleviates the problem. I would hate to have to manually update 21 calculations every time a pin status changes so I am looking for another solution.
Thanks for the help
Too much things in a single layout...
I don't kmow which device you are using but I think also an iPad Retina will be slow...
In order of relevance you should act in this area:
You've already found the cause of the slowness, you're in fact overloading the layout with unstored calc fields and the resut you get is expected
In general (but YMMV) data entry might/should be kept separate from other info, thus resulting in a much more clean and fast user experience ... then you could have the user land on a second layout that would show the other data
You might in addition try to replace unstored calc fields with stored indexable fields: I don't like a lot this approach, since it would imply to use procedure(s) to write the result of the calc into the stored fields, and procedure(s) to keep them updated, but it might help a lot from a performance point of view
Retrieving data ...