Before trying any of these steps, we recommend reviewing this article Calculation Issues to properly identify the slow formulas in your doc.
When your doc is done calculating, the performance tool will give you a sorted list of which tables took the longest to calculate.
- Expand the name of the table to see which specific columns are slow.
- Click the name of the table to navigate to it, then open the formula that is slow.
- If you no longer need it, delete the column or formula.
- If you do need it, look through the list of Common Issues below. If your formula has one of the issues on the list, follow the instructions to fix it before looking at the General Guidelines.
- If you don't see your issue on the list, read the list of General Guidelines at the bottom of this page for more ideas of how to optimize your doc.
- If nothing else works, please send us a message with a screenshot of the formula and we can look at it for you. We're happy to help!
Common issues with Filter() formulas
The examples below refer to the Filter() formula, but the same tips apply for similar formulas like Lookup(), CountIf(), AverageIf() etc that have to look overall the values of a table.
Dereferences inside a Filter():
One of the most common patterns we see in slow formulas is the use of a dereference inside a Filter() formula. For example the formula below is dereferencing the value of the Name attribute of a row reference stored in Column 3 of Table 1 inside of a Filter() formula.
To fix this formula, remove the dereference from inside the Filter() query. Create a new column in the source table that is being filtered, in this case Table 1. The formula for that column should be same as the one inside the Filter() formula. In this case Column 4 stores the value of the Name field of the row reference stored in Column 3.
Once you've created a new column in the table that is being filtered that stores the result of the dereference, you can change the original Filter() query to just use this new column instead. In this example, you can replace the original Column 3.Name with the new Column 4 since it has the same formula.
Calculations inside a Filter() formula: Similarly, doing calculations inside of a Filter() formula isn't optimal. For example, the formula below performs a Count() operation on the number of row references stored in Column 3 of Table 1 inside of a Filter() formula.
Similar to the previous example, the calculation needs to be removed from inside the Filter() query. Create a new column in the source table that is being filtered, in this case Table 1. The formula for that column should be the same as the one inside the Filter() formula. In this case, Column 4 stores the result of the Count() operation for the list of row references stored in Column 3.
Once you've created a new column in the table that is being filtered that stores the result of the calculation, you can change the original Filter() query to just use this new column instead. In this example, you can replace the original Column 3.Count() with the new Column 4 since it has the same formula.
Do not use IsNotBlank() inside a filter formula: The optimizer does not currently work on the IsNotBlank() formula. For example, the formula below finds the rows from Table 1 that match a certain value but also filter out blank rows by using the IsNotBlank() formula:
Similar to the previous examples, the IsNotBlank() formula needs to be removed from inside the Filter() query to prevent it from being calculated unnecessarily. In order to do that, you need to create a new column in the source table that is being filtered, in this case Table 1. The formula for the new column would just check if the relevant column is blank and store true or false. In this case, Column 4 stores whether Column 2 is blank or not.
Once you've created a new column in the table that is being filtered, you can change the original Filter() query to just use this new column instead. In this example, you can replace the original Column 2.IsNotBlank() with Column 4 = true.
Do not use repeated Filter formulas: Often docs with many columns or views will have different versions of the same Filter() query across multiple columns or conditional formats. At the moment, Coda does not share calculations across multiple columns that have similar formulas so it's a good idea to try to reuse them manually. It can almost eliminate the additional calculation time for each formula that is repeated, making a big difference in docs that have many similar formulas. In order to do this, just break out the part of the formula that is common across formulas into their own column and then refer to it rather than looking it up again. For example, the following two columns have very similar Filter() queries:
Instead of running the same Filter() query twice, you can create a new column runs the filter query, as follows:
Once you have created this column, you can replace the original formulas with the following and the results will still be the same:
This technique also applies if only some part of the Filter() query is shared across multiple formulas. For example, there is one condition that is shared between the two formulas below:
You can still create a new column with a formula that calculates only the part that is common, as follows:
Once you've created this column, you can replace the original formulas with the following and the results will still be the same:
Notice how the second column is still using a Filter() query but this time it is only filtering through the values already stored in Column 4 rather than all the rows of Table 1.
Replace If(Filter(X),Filter(X),0): An If() statement where 2 of the parameters contain the same Filter() query will evaluate it twice for the values that match rather than just once. You can make things go faster by breaking out the Filter() query into it's own column. For example, let's say you have the following formula:
As you can see, this formula has the same Filter() query being evaluated twice. Instead, you can create a new column that runs this filter query beforehand and stores it. In this case, it would have the following formula:
Once you've created this column, you can replace the original formula with the following and the results will still be the same:
Replace And(X,Y) with X AND Y: Switch to writing AND between each of the conditions. For example, replace this formula:
with this one instead:
Replace Filter(X).Filter(Y) with Filter(X AND Y): If your formula contains two or more filter queries, you can replace them with a single filter query with multiple conditions joined by AND. For example, replace this formula:
with this one instead:
Do not use formulas like CountIf(), AverageIf(), SumIf() etc: The optimizer currently does not work on formulas like CountIf(), AverageIf() and SumIf(). Instead these formulas can be replaced with a Filter() formula. For example, replace this formula:
with this one instead:
Make valid comparisons: If you are comparing 2 values, like inside a Filter() formula, make sure that they are of the same type. For example if you are comparing an entire column of values to a single value as follows:
In the part inside Filter() in the Formula above, the icons indicate that the left hand side returns a list of values while the right hand side is a single value. Instead the correct formula would be as follow, where the types on both sides are the same:
The same rules apply to view filters: If you write a filter formula for a table or view, that is similar to writing a Filter() query even if you aren't writing a Filter() formula yourself. So if your Filter formula is dereferencing a value, or performing a calculation or any of the other anti-patterns in this list, it's a good idea to break that calculation out beforehand and just use it in the filter formula.
General guidelines
- Use Today() instead of Now(): If you are using the Now() formula, it recalculates every second. Consider switching to the Today() formula instead if that can serve your need.
- Write more atomic formulas: If your formula is performing some complex set of calculations, you can split it up into several different columns or formulas with each one being more atomic. This allows Coda to optimize as many of those calculations as possible instead of recalculating them unnecessarily. This is also helpful in case some of those calculations are repeated across different columns, and you can just reuse the results rather than forcing Coda to recalculate them each time.
- Fix any errors: Coda is much better at understanding & optimizing formulas when they are error free.
- Choose the right column formats to match the type of data: Coda allows you to put data in a column even if it doesn't match the column format. However, if you do not choose the correct column format Coda has to be more cautious in calculating them rather than making assumptions and optimizing them. Choosing the right column format can also improve calculation performance.
- Use good schema design: Sometimes, an incorrect structure of your doc can force Coda to perform unnecessary calculations and slow things down. Good schema design can not only make your data easier to access and work with but can also help optimize the calculation performance.
Take a look at the example below. This table contains a list of different Meetings that took place with different clients. The last column calculates the number of meetings that you have conducted with that particular client.
However, as you'll notice, the formula returns the same result for all the different rows that have the same client. It is being calculated separately for each row, but the result will always be the same. This may not matter when you have a small amount of data but as your doc gets bigger, these unnecessary calculations will start to add up.
In the example above, it's much better to make a separate table for Clients and change the Client column of the Meetings table to be a Lookup to the Clients table. Once you've done that, you can then calculate the number of meetings you've had with each client by creating a column in the Clients table. This is much more efficient since it calculates once per client rather than once per meeting. Here's what it looks like:
If you still want the information about the number of meetings with that client to still be visible in the Meetings table, you can now just write a formula to look it up from the Clients table rather than calculating it for each Meeting. Here's what that formula would look like:
Do not repeat calculations that do not differ for each row: When you write a formula in a column, Coda has to calculate that formula for each row in that table. While that cannot be avoided in many cases, sometimes the result you are calculating doesn't actually change from row to row. In those cases, it's better to not make that formula recalculate by converting it into a named formula in your doc. For example, consider the formula below:
The Filter() query has one condition that changes for each row in the Clients table but the second condition is not dependent on the current client being looked up. Rather than performing these calculations in each row of the Clients table, it's much more efficient to first calculate the part that is the same across all the rows in a named formula in your doc. To do that, just type = somewhere in your doc and write a formula and give it a name. In this example, here's what the formula would look like:
We've named the formula "Today's meetings" (by clicking on the label that said "Unnamed Formula") so we can now reuse it in other formulas in our doc. We can now go back and modify the original column formula to the following:
This formula will run much faster since it is only calculating the set of Meetings that match Today's date once and for each row in the clients table, it is only searching in that set rather than across all the meetings.
Reduce number of views: In some docs, the number of views is so large that the filters and conditional formats on all those views take too long to calculate even if no individual column in the table is slow. This is not usually an issue for docs with several dozen views but we've seen docs with much higher numbers of views where the views themselves took 10-20 seconds to recalculate each time the data in the doc changed. If this is the case for your doc, you might want to consider using an interactive control to filter a single view rather than creating lots of views for each value.
Reduce the pack refresh rate: In docs that have packs, loading the most up to date data from some packs can prevent other calculations from completing. You might want to consider reducing the frequency of these refreshes or setting them to be manual rather than scheduled to be every hour or day.