Background

When we took on the challenge of boosting our formula language, we knew we had to juggle three different and competing goals:

• Make things simple for builders trying to solve difficult business problems.
• Ensure end users have a delightful experience consuming what their builders create.
• Protect the platform – as we are a SaaS (Software-as-a-Service), we need to balance the traffic on the highway to ensure everyone has a great experience.

To recap 2021, Quickbase formulas were taken to the next level with two major features:

1. Formula queries: simplifying and expanding the class of problems Quickbase can solve.
2. Summary report formulas: allowing you to perform advanced calculations on aggregate data.

These are investments we made in our core engine, a special part of Quickbase that offers unparalleled flexibility and speed.

As you know, all platforms have limits. Some more than others. We usually try and favor allowing creativity, with modest limits compared to other platforms. The balancing act there is build-time vs runtime. Platforms can take educated guesses at what end users are going to do, and then they have to choose between being too restrictive or too lax.

We also have a responsibility to help and educate builders to create the best possible applications they can with good design choices. The challenges and opportunities are endless, especially when Quickbase applications are growing faster than ever, with even greater levels of complexity. Fun fact: One customer's application hit 650 tables this year, in production (don't try this at home).

Our Limits

In our case, one of our runtime limits is a prediction engine on reports and queries. Rather than letting an operation run on for too long, we sample a subset of the records to determine how long it will take and extrapolate that out over the size of the report to determine if it needs to time-out. That is when you see the "Uh-Oh!" error, shown in the following image. The important thing here is that in 100% of cases, customers are able to create a report serving their needs by optimizing. So, a little oil change and tire rotation helps builders create better applications, that their end users enjoy more, while protecting the platform for all users.

NOTE: Let's take a minute to look under the hood, to explore the performance implications of the error message above: Even performing this calculation takes time. Meaning, this check and balance costs us a fraction of a millisecond on every report. That is a worthwhile trade-off, and one all software platforms take. But there are diminishing returns. The more toll booths and weigh stations added to the highway slow down the flow of traffic. Think about how Quickbase permissions allow reports to calculate different results for different people – all in real-time. What if I gave you the option of never having your end users see the "Uh-Oh" error shown above, but the trade-off was that we checked it for safety during report creation? In order to meet that goal, we would need to run your report for someone in every role in the application when you click "save". Now the millions of report changes every month have overhead which they did not have before. That trade-off isn't worth it, especially since we don't know how often that report will even get used.

When creating formula queries, we needed to strike the right balance of allowing creativity while also continuing to have safety nets on the platform.

Build-time protections

Let's start with a relatively simple formula query: SumValues(GetRecords("{6.EX.'In Progress'}"), 4). When saving this formula, we don't know if it will be used on a report with 1 record, 1 million records, or used for permissions (which is the most expensive operation possible performance-wise). We have to take a guess. On one hand, we could assume the absolute worst. That would be seeing how long it takes to save the formula for one record and then multiplying it by the number of records on the table and seeing if it exceeds a threshold. This is the most restrictive, because it isn't likely that all operations leveraging the formula query will require all records. This would offer the most protection, but stifle innovation. On the other hand, we could just multiply it by some low value like 10, which would let almost every formula save (preventing only the absolute most erroneous from saving) but then that could be problematic at runtime. And this is a constant balancing act. Think of this like tuning a car's engine. Some cars optimize for acceleration, and others optimize for torque. It isn't right or wrong – just choices that need to be made. We have settled somewhere in the middle, that is dynamic to each table. And as we assess performance and the data we have, we will continue to refine the algorithms further.

One additional nuance is that it is conceivable a formula gets through the builder checks on day 1, but something changes on day 2. Maybe a million records were imported, or an underlying field got more complex. In that case, you might see us stop the formula from saving on day 2.

Runtime protections

We can't count on protections just at build-time. What if the formula is saved when there are no records in the application because it is still being built? Or what if a certain role has very complex permissions? Build-time protections will pass, but it could be dangerous. When running a complex report, we would have allowed a non-optimized formula through the front door that may be extremely slow or introduce stability issues. The game changes. And because we run our own purpose-built in-memory database, we are actually considering these things down to the physical blocks of memory on the servers. This is exponentially more difficult when you factor in that a formula can be used inside of a permission, or inside of a summary field – it is infinite.

Using any expensive formula to sort, for example, can cause us to do millions of additional calculations that aren't perceivable to the builder or user. This is more likely with the introduction of formula queries.

And what if the formula query is inside of a field with a dependency diagram like the one shown below (it isn't even fully expanded!)?

That's why a table with 5,000 records may run slower than one with 10,000,000 records – the underlying complexity of the "questions" you are asking Quickbase. And so to account for this, we added some logic special for formulas to that prediction engine I mentioned at the beginning.

What you can do

Enterprise software needs maintenance. Quickbase applications represent a no-code world where the amount of maintenance is much lower than traditional software, and even low-code.

 

Within the context of Quickbase, the oil changes and tire rotations include things like optimizing reports and tuning searchable fields. 

Dependency Diagram

In all of your complex applications, become best friends with the Dependency Diagram! It can be accessed from field usage, on any derived field (formulas, lookups and summaries). It is one of the "stickiest" builder features in Quickbase. Meaning those of you who use it, come back and use it often. It helps you visualize how your fields interact with one another and can easily uncover hidden complexity. This is a great place to start during application maintenance or troubleshooting.

Performance Analyzer

Next, ensure the Performance Analyzer is enabled in your application properties. This is a valuable tool for you to understand how your application performs in all ways, not just formula queries. If you've turned on "Analyze Performance" in your session (by clicking your name at the top right), saving a formula query that gets stopped by our protections will offer an additional option to quickly get more details:

This is going to give you more specifics around what is being caught during the save operation. When running a report, we can even see that the sort time is being consumed by our formula query field. Under the hood: sorting by ID is listed here, even though it wasn't explicitly defined, because we need to tie-break record sorting.

Searchable Fields

You should regularly optimize and streamline searchable fields in all tables. We recommend selecting 10-15 fields per table which are most important for your users to search on, and mark the rest of the fields not-searchable. Pay special attention to complex derived fields, like formulas containing a query. This is one of the most important things you can do to keep your app running smoothly.

Tech Support

Our Tech Support team is also available to help answer questions. They also have access to more tools that can point you in the right direction.

Customer Success and Solution Architecture

We have a team of Customer Success Managers and Solution Architects that not only can help reactively, but on a proactive basis as well. We can help analyze your application in extreme depth, understand your usage patterns, recommend, and help implement solutions that scale. Our 20+ years of expertise combined with the tools in our toolbox can be leveraged to its fullest potential, helping you scale your Quickbase solutions to thousands of users enterprise-wide.

We will update resources as we gather examples to help illustrate opportunities to improve performance in formula queries. In the meantime, please reach out to your account team to discuss additional ways we can work together.

------------------------------
Harrison Hersch
------------------------------