OK, I’ve sorted out which columns seem to be causing the performance issues.
I have a column which shows the potassium to sodium ratio for the day (K/Na (day)):
For any given row, K/Na is very simply to calculate. The formula is just “potassium / sodium”.
However, for a group of rows (for example, a meal or a day), you take the potassium for the group and divide by the sodium for the group.
So, our “K/Na (day)” formula looks like this:
Alright, that is not an expensive calculation. However, let’s look at what goes into each of “K (day)” and “Na (day)”. Here’s “K (day)”:
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
- Take the entire table and select rows where the date matches the current row’s date
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. 