Performance of table view with groups

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. :slight_smile:

Link to the doc:

https://coda.io/@eduardo-cavazos/copy-of-health

Thread where I originally shared the doc:

Thanks!

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

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. :slight_smile:

Here’s a video where I talk about the issue as well as a feature request (group formulas) that might be one way to solve this issue:

The idea is that you’d have an additional option in the following menu:

where you can specify a formula that is applied at the group level.

In my case, the formula might look something like this:

Group.Potassium.Sum() / Group.Sodium.Sum()

in order to calculate the potassium to sodium ratio for a given meal or day.

Hi @Eduardo_Cavazos,

Thank you for posting this great breakdown of the issue!

Here are a couple help articles on improving performance that may have some tips that apply as well…

1 Like

OK, I have mostly addressed the performance issues with the ‘Health’ table and associated view.

The following are the computationally expensive columns:

  • K (day)
    • thisTable.Filter(Date=thisRow.Date).Potassium.Sum()
  • Na (day)
    • thisTable.Filter(Date=thisRow.Date).Sodium.Sum()
  • K (meal)
    • 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. :slight_smile:

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.

@BenLee,

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.)

@Eduardo_Cavazos

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:

  1. The RED COLUMN runs the filter for the entire table in every row of the table only one time (in one column).

  2. The ORANGE COLUMN runs a filter on a tiny sub-set of the table-wide filter from the red column.

  3. 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! :grinning:

4 Likes

@Ander,

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:

in the following section:

I should have read it more carefully. :slight_smile:

Anyway, thanks for your help and patience @Ander!

1 Like