You have correctly identified your problem. Grasshopper.
Well, pretty close, anyway. It's not necessarily SQL that's the issue, but the large number of unstored calculations. Aggregation of statistics using unstored calculations will be a performance killer, especially as the record counts start to climb. You're far better doing your aggregating in a warehouse table as you go, using either Script Triggers or batch jobs run on a schedule. This will allow FileMaker to run the aggregates as stored calculations, which will process much faster.
So for every stat you want to keep, you need to write some code to store the value and then update it as the data change. That's the basic strategy behind warehousing.
Is your solution hosted? And how often does the dashboard need to be updated? If it is hosted, you may want to consider a scheduled script on the server to periodically update the values in a separate table as Mike suggests.
nice modern interface :-)
Sadly enough you have to keep in mind though, that FileMaker is just damn slow. So a live dashboard like your´s probably is kind of impossible with larger datasets. So indeed the best idea might be to just show some static values and then offer a "Refresh" button together with progress bar.
FM is plenty fast.
As Mike indicated, SQL is not the culprit here but the sheer number of unstored calculations. Instead of calculating these things on the fly, have those totals updated as part of the workflow. For instance when a new donation is entered, update the totals field. That way it becomes just a number field that carries no penalty when you display it on the dashboard.
i'll agree with Wim. Filemaker is plenty fast, but a developer can make decisions that will create a slow solution.
I'll hope to hear back when the fields referenced in your dashboard are done with stored calcs or static values. How much faster is an interesting question. It would be instructive for all of us, to be sure.
As others have indicated, the issue is the number of Un-stored calculations. Wim gives you a good suggestion for setting this up as stored values that can be indexed. This will speed the whole thing up.
How ofter do you need the display to update?
Here are a couple of other options for updating the values. The first is a script that runs all of the queries, puts the values into Variables, and then set field, or Set field with the SQL as the value. Use an On timer script attached to the window to run the script every X minutes.
Another option is to convert all the Un-stored calculations to Auto-Enter Calculations. Add a field call it ~update and add it to the let statement in every Calculation. So you would add
upd = ~update;
Everything else that is already there ;]
Then have your On timer script just has to change the value of the ~update field. This will force all of the auto enter calculations to update. In short you are making all of the auto-enter calculations dependent on the ~update field.
The result will be a stored data so the screen opens quickly, and the update happens in the background.
I've just updated the "top 3" calculations to be indexed and I can already see a HUGE performance boost. I think we're on to something!
@Mike_Mitchell or wimdecorte
How do I force the totals fields to update via a script trigger? More specifically, what script step is capable of doing this. I think this may be the route I'd like to take because I would prefer that the dashboard is updated as the user makes changes.
Thanks! I do love myself some flat design. I'm still working away at the icons...probably the slowest part... I can't take too much credit though, many of the layouts are really just an adapted version of the invoices starter solution.
Set Field generally works pretty well.
You usually have to do some error trapping for record lock (to make sure two users don't collide with each other and attempt to update at the same time), and you have to watch your context, but you can generally just use a Set Field script step.
Don't automatically start thinking "script triggers", there is usually a flow that you can take data and run button-driven scripts instead of having to carefully piece together a set of independent triggers that can all step over each other.
Triggers certainly have their place but they should not be the first go-to here.
Yes, true. My original mindset was, "Update field, update total." But if you have a scripted workflow process, by all means, incorporating the dashboard updates into that is a much better plan.
Woops, I have to watch my wording. I have some buttons for making a donation or updating a membership that could drive the extra script step and update the totals.
Do you know where I can delve deeper into the mentioned error trapping/record lock? We're only running three accounts of filemaker 13, but I think it would be best to study up and avoid this error.
If I use the set field step do you select the field you want to update and just set it to it's own calculation already stated in the field calc?
Scratch that, I didn't see Bruce Herbach's post until now. I think that answers my question about the set field step.