Hello! Have a question that would be great to get some ideas on. I have a table that has some really complex calculations (it uses other tables, queries data over a long time range, conditional filtering, etc). From using the calculations analysis, I can see that specific column/formula is taking a long time.
This is what my options look like moving forward:
optimize the formula (take advantage of indexes, not sure what else)
perform “one-time” calculations that store the result in this table, versus a live formula
Any thoughts on how I could go about accomplishing this? Does option 1 or 2 make more sense, and how could I trigger an event like option 2 periodically?
Depends on the situation really. E.g., how often you make changes to the data that trigger recalculations; what kind of “long time” we’re talking etc. Option 2 is certainly a good idea for many cases. But it also may happen that the bottleneck is something that could be easily avoided with some minor reorganization, like this.
TL’DR: need to review each case individually.
Re option 2, here’s what I’m doing for example. There’s a report that takes quite some time to generate (several minutes). So I encourage to do it only on demand, but provide indication that no refresh is required (based on whether ModifiedOn.Max of source tables is earlier or later than such of the generated table)
When I encounter situations like this, I consider scale and performance and typically build such computations into a server-side process that can update conditionally at a set interval. The interval is the downside and easily remedied with webhooks.
Separately, Coda developers should possibly think about filter computation caching. (what am I saying - of course they’ve thought about this)
I was working with Streamlit this week on a machine learning project and I noticed that deep computations involving large data sets were almost instant. This was on 2 million records. I assumed it was so fast it could not possibly be working. But it was. Smart dudes over at Streamlit.
Thanks Paul, these are helpful tips. I will go through the optimization steps outlined by Coda first, and then resort to your second approach if necessary. My reports aren’t in the > 1m range yet so I’m hopeful.
