In this video at 14:25, I demonstrate a performance issue I’m seeing with a table view that has grouping enabled:
Note that after I add the ‘Fage yogurt (200g cup)’ row, it takes around 3 seconds for the zeros to show up in the row.
Then after I enter the amount (1), it takes another 3 seconds or so for the formula values to compute and render.
I like to do a lot of “what if” exercises in the table by adding and changing values to see their effects, but with latency like this (3-6 seconds) it’s really a non-optimal experience.
I’ve seen a few threads on here regarding performance of tables with groups so I just wanted to add in another vote improving table group performance.
Ding-ding-ding! This is the expensive formula… In words:
For each row
Take the entire table and select rows where the date matches the current row’s date
Sum the potassium of this subset of rows
We do something similar for “Na (day)”.
There are also “K (meal)” and “Na (meal)” columns. If you want to try this out yourself, remove all of these “(day)” and “(meal)” columns and you’ll notice that adding new entries is fast again.
Suggestions of more efficient approachs to calculating “K (day)” and “Na (day)” are welcome.
thisTable.Filter(Date=thisRow.Date AND Time=thisRow.Time).Potassium.Sum()
Na (meal)
thisTable.Filter(Date=thisRow.Date AND Time=thisRow.Time).Sodium.Sum()
I more often care about the K/Na for the day, instead of just for the meal. So I removed the latter two columns. Note that they are slightly more complex than the first two.
With that change, the table performance is once again acceptable.
Of course, it would be nice to at some point re-introduce the column for K/Na (meal), so if the performance of coda is improved in such a way that this column would be made faster, please let me know.
Also, as mentioned in a previous post, please consider adding a ‘group fomula’ feature as I believe this approach would be much more efficient than the approach currently demonstrated here.
Thanks for the references to these docs. They both contain valuable advice.
For those who haven’t looked through these, the first contains directions on how to use a profiler to determine hot spots in your tables.
The second document has a lot of tips on how to optimize formulas.
As far as I can tell, these didn’t apply to the formulas in my health doc that were computationally expensive, but they were still interesting to review and I’m glad I know about this list now, for future reference.
(See my previous note for details on how I improved the performance of the doc.)
Per your post above, you were running a filter for the entire table in every row of the table four times (in four columns). After removing two columns (which you would prefer to keep), you are still running a filter for the entire table in every row of the table two times (in two columns).
In the demo doc below:
The RED COLUMN runs the filter for the entire table in every row of the table only one time (in one column).
The ORANGE COLUMN runs a filter on a tiny sub-set of the table-wide filter from the red column.
The GREEN COLUMNS run computationally insignificant calculations against the red and orange columns.
Maybe you can hang on to those two columns you wanted after all!
Thanks for taking the time to explain this approach! I see now that you are factoring out the expensive portion of the formula (which is repeated) into a separate (and reusable) column.
I also see now that this was recommended in the following document:
And one more thing! Improvements to calculations (and Coda elements that waited for calculations to complete before rendering) have been rolling out the past few days. Learn more here: Improvements to Calculation Performance