What’s in a millisecond?

By most standards, a millisecond isn’t a lot of time. It’s probably even less time than you think. Just saying the word “millisecond” takes about a thousand of them – or, as its more commonly known, one second.

Ever done something “in the blink of an eye”? It takes somewhere between one hundredth of a second and four hundredths of a second – or 10 to 40 milliseconds– to blink.

What about a photo finish at the end of a race? That’s getting closer, but even the closest finish in Formula One history was decided by 10 milliseconds, not just one.

How about something even zippier, like the entire discography of Minor Threat? While notoriously short, that actually stands at about 47 minutes – otherwise known as 2.82 million milliseconds – of hardcore punk perfection.

So if we told you about a new Grist feature that could help you save a few milliseconds at a time by identifying opportunities to optimize your spreadsheet formulas, you might roll your eyes. But those fractional time savings quickly add up to significant ones.

And for the record, that eye roll cost you about 800 milliseconds.

From milliseconds to minutes

Let’s say you’re working with a data set that consists of 500 rows. Using Grist, you create a column formula to calculate some summary values. Each of the 500 instances of that formula executes in 1 millisecond. From start to finish, it will take around 0.5 seconds for your document to load with the results of that column formula included. No problem.

Now, let’s say your data includes 50,000 rows instead of 500. With the same column formula, it will take 50 seconds for your document to load.

In order to improve load times, you might need to optimize your formulas so that Grist can execute them faster. But before you can do this, you need to know which formulas are slowing down your documents. Enter Grist’s new formula timer feature.

Timing formulas with Grist

Grist’s new formula timer measures the runtime for every formula included in your document, telling you how long it takes in total to calculate each column that includes a column formula, as well as the average time it takes to calculate cells within those columns. By sorting and analyzing the data, you can quickly identify time wasting outliers and formulas that are ripe for optimization.

The formula timer includes two options: Start timing, and Time reload.

Start timing keeps the formula timer running in the background as you work in your document. Once you’re ready to analyze the runtime data, you can stop the timer and view the output. When using Start timing, Grist will constantly monitor formula runtimes until you manually end the process.

Time reload forces your document to reload from “cold” and calculates the runtime of each formula, providing you with a baseline reading to quickly identify areas of needed improvement in your formulas.

Both options provide the same output: runtime data formatted in a Grist table, with the same sorting and filtering options you’d expect in your documents.

The Total Time (s) column calculates the total time required to calculate each column formula (Column ID) in seconds. Sort from largest to smallest values to quickly identify which column formulas are prime candidates for optimization.

Let’s take a look at an example of Grist’s formula timer in action.

Scenario: Timing and optimizing an expense document

Here, we have a modified version of Grist’s Credit Card Activity template with an added column (% of month) calculating what percentage of the month’s total expenses each line item contributes to.

The column uses the formula:

month_total = SUM(Activity.lookupRecords(Month=$Month).Expense_Amount)
$Amount / month_total

The formula defines a new monthly total value by looking up and summing all of the individual expenses for the month in the Activity table, then dividing the line item by that total. By using the formula timer, we can assess how well our new formula is performing.

It only takes Grist 0.012 seconds to calculate the new formula across all 180 records, but its still the slowest user formula in our document (columns starting with # are internal helper columns). In a document with 4,000 records, the formula would take nearly 5 seconds to calculate. This is because of quadratic complexity: as the number of records in the document increases, so does the number of items that the formula needs to sum. If our document was much larger – say, 80.000 rows – it would take more than 30 minutes just to compute this one column.

We can prevent this problem by optimizing our formula. The monthly total variable that we defined is already being calculated in a summary table. Instead of recalculating the value each time the formula is executed, we can lookup the value in the summary table instead. Our new linear formula looks like this:

month_total = Activity_summary_Month.lookupOne(Month=$Month).Expense_Amount
$Amount / month_total

This method prevents Grist from performing time-wasting calculations each time it calculates our formula. We can again use the formula timer to see how effective our change is:

Now, our formula calculates in just 0.008 seconds – a time savings of 33% with one simple change. If our document were to grow to 4,000 records, we’d expect the calculation to take around 0.16 seconds instead of nearly 5 previously.

How to use the formula timer

From any Grist document, open Settings at bottom left and locate Formula timer in the Data Engine panel. Choose between Start timing and Time reload. When timing is complete, Grist will display the results in a table. Sort by total runtimes to identify formulas with the longest runtimes, and optimize them as needed.

Note that the formula timer output table will not be retained if you navigate away from the page. To retain the data, copy and paste the information into a Grist table, or keep the output table open in one tab and reopen the document in another. Visit the Grist Help Center for more information about using the formula timer.