Give Your Apps a Tune-Up using the Performance Optimizer
We are proud to announce the availability of our new Performance Optimizer. This is a tool which you can run to scan a Quickbase app for performance optimizations. And the best part is that we can apply those changes to your app with a single click. This feature will be added in our September 2021 product release.
(The Performance Optimizer is available on Enterprise plans only.)
Background
Over its 20+ year history, creative app builders have consistently pushed the limits of what’s possible with the Quickbase platform. Whether it’s apps with conditional fields that summarize data based on millions of child and grandchild records, or types of relationships that we had never dreamed of, this community of builders continues to inspire us every day. And what many of you have been able to achieve in hours or days using Quickbase would be impossible on other platforms without years of effort and massive investment on the part of professional software development teams who already lack the resources to meet the needs of the business.
Quickbase has always demonstrated industry-leading performance, but the work of delivering an enterprise-grade platform is never finished. Our senior engineers and software architects are always looking for opportunities to further speed up the performance of the Quickbase platform. As Product Management Director Harrison Hersch discussed previously on the Community, we split our performance work into three categories:
- Infrastructure updates – improving the hardware and security layers of Quickbase to speed up the platform as a whole
- Software optimizations – improving our proprietary, in-memory database engine so all Quickbase apps get faster and more efficient
- Helping customers maintain their applications – making it easier for app builders to improve and optimize the unique solutions you’ve built on Quickbase
We continually invest in each of these three types of performance improvements. However, app performance is a shared responsibility. You could consider adding a fourth type of performance work to this list: - App builders periodically tuning their apps – Our preferred way of making Quickbase faster is when you as an app builder can wake up one morning, and with no time spent, your apps just run faster than they did the day before. And Quickbase abstracts away most of the routine maintenance work other platforms require. However, since Quickbase apps are so innovative and vary so widely in structure, there will always be some opportunities to improve performance that requires a bit of an app builder’s time and judgment to apply appropriately. This should be done periodically to make sure your app is as flexible and scalable as possible.
The Performance Optimizer is the latest tool we are giving app builders to tune their apps.
What types of changes the Performance Optimizer can make
One of the ways you represent your complex business logic in Quickbase is by creating sets of filters. You set up these filters in many places, such as for reports and when setting additional criteria on summary fields. You can include many lines of criteria on the same report, for example. But the order of these filters can affect the performance of your app.
The Performance Optimizer can make the following types of changes to speed up your apps:
- Re-ordering the additional criteria on summary fields
- Re-ordering criteria in custom rules for permissions
- Re-ordering report filters
- Removing unused multiple-choice options
How re-ordering criteria affects app performance
Imagine you have a table with 250,000 warehouse delivery logs. This is a table which has existed for many years, and you want to create a report to show completed deliveries from a specific supplier (We’ll call them Acme Co). Perhaps you need this report because you are validating the on-time performance of a new supplier. To create this report, you could set up these filters:
- Status is equal to Completed AND
- Supplier is equal to Acme Co.
These filters will produce the correct result. However, they will do so in an inefficient way. Since this table has existed for several years, and the Acme Co. supplier is new to your business, we can make a few reasonable assumptions about the data in this table:
- The % of delivery logs that are for Acme Co. is small (because Acme Co. is a new vendor)
- The % of delivery logs whose status is completed is large (because of the age of the table)
This means that the order of the filters above will cause Quickbase to first scan all 250,000 delivery logs to see which ones are completed. Let’s assume in this case that 249,500 records meet this criteria. Next, Quickbase scans all the records where the status is completed and checks to see which of those are related to Acme Co. Let’s assume 45 of the records are for Acme Co, and 40 of those have the status of completed. This means Quickbase first scans 250,000 records, then scans 249,500 records, and finally displays the report – showing just the records that meet both criteria. Before displaying the report, Quickbase needs to scan a total of 250,000 + 249,500 = 499,500 records.
What would happen if we flipped the order of the criteria?
- Supplier is equal to Acme Co. AND
- Status is equal to Completed
This new ordering will first cause Quickbase to scan all 250,000 records to determine which delivery logs are related to Acme Co. This matches 45 records, as we saw above. Next, Quickbase scans those 45 records and finds that 40 of them have the status of completed. Before displaying the report, Quickbase needs to scan a total of 250,000 + 45 = 250,045 records. This reduces the number of records Quickbase needs to scan to run this report by 50%.
Updating these types of simple filters can be done manually without much trouble. However, this is a simple example designed to explain how filter order affects Quickbase app performance. In real-world applications, reports/custom rules/etc. are far more complex. Filters with 10 or more lines of criteria are common. Criteria are often grouped together to implement complex business logic. And if some fields in the filters are formulas or summary fields which involve their own calculations, it becomes that much tougher for an app builder to figure out the best order for their filters. The Performance Optimizer, on the other hand, runs all the performance metrics for you.
This is a great example of how spending a little time to tune an app’s performance can have major benefits.
NOTE: Compared to the example above, the performance benefit of re-ordering filters is exponentially greater for custom rules in permissions. This is because Quickbase automatically runs those rules every time data on that table is queried. So it applies not just to a single report, but to all reports, charts, email subscriptions, dashboards, etc., on the table.
How do unused multiple-choice options affect app performance?
When you update the properties of a Text – Multiple Choice field, you can switch to use a standard text input where the user can type in any text. When you do so, Quickbase remembers what multiple-choice options you were using previously. This gives you more flexibility to make further changes to the field later. For example, it allows you to revert the field type to use a multiple-choice list again, with the options you had before. However, many app builders do not revert such text fields back to use the multiple-choice list. In that case, when Quickbase needs to save schema changes you've made to your app, keeping those unused options slows down that process.When to use the Performance Optimizer
When developing the Performance Optimizer, we saw a great opportunity to empower app builders to tune and speed up their apps. When you run the Performance Optimizer, the entire app’s schema is scanned, and a list of performance optimizations is displayed. We designed this tool so it will not interfere with your app users doing their work, so the Performance Optimizer can be run at any time of the day/week. How long the Optimizer will take to run depends on the complexity of the app.
We recommend you run the Optimizer on each app your team uses regularly. (You do not need to run it on personal apps built for yourself. This is because there will not be any performance improvements in that case.) For your most complex and business-critical apps, you should consider running the Performance Optimizer on a monthly basis. Also, please keep in mind that the Optimizer will take longer to complete for larger and more complex apps.
How to run the Performance Optimizer
To run the Performance Optimizer, open your app’s settings, then click App Management. We’ve added a new option for the Optimizer on this page. When you run the tool, you may choose to run it in simulation mode. With simulation mode on, we will provide a list of performance optimizations but will not implement those changes in your app.
If you run the Performance Optimizer with simulation mode off, we will find and update your app’s schema to implement those changes. The Performance Optimizer is the first feature we’ve built which updates your app schema for you, so we have tested it extensively to guarantee that it is simple and safe for you to use. We have built in checks and balances to ensure that we will only make a change where we are sure it will speed up the app. And our Technical Support team is always available to help with an app restore, if you decide you would like to revert to the schema you were using before you ran the Performance Optimizer.
