In order to make apps run faster, you need to first understand some core concepts.
Ideally, the users of an app should never need to wait for the app to sync data or react to inputs. A well-tuned AppSheet app can come close to achieving this ideal.
AppSheet apps run on a mobile device or browser. All the table data used in the app is maintained in a local copy on the device or browser. This local copy allows the app to be interactive, responsive, and run offline. The platform needs to ensure that this local data copy stays in Sync with the true source of the data which is in a cloud data provider (like Google Drive or Office365 or SQLServer).
The following sections provide considerations for improving app performance:
- Latency versus throughput
- Improve the speed of sync
- Improve app performance
- Performance considerations for common data sources
When we refer to the performance of the app, we typically mean the responsiveness of the app as perceived by the end users of the app. The term latency means time spent waiting. App responsiveness is directly related to perceived latency. Our goal is to minimize latency perceived by end-users.
As you understand how AppSheet apps work, you'll see that there are different systems communicating with each other. Each communication involves the transfer of data between the systems. In this context, latency refers to the time a system takes to initially respond to a request. The term throughput refers to the amount of data that can be transferred per second. So for example, to fetch a spreadsheet from Google Drive, the overall latency is the sum of the initial latency to respond to the request and then the time taken to make the actual data transfer.
The AppSheet app cannot directly connect to the cloud data provider. Instead, it communicates to the AppSheet backend service (the AppSheet server in the figure below) which then acts as an intermediary to communicate with the cloud provider. This tiering is necessary for the sake of security but also has performance benefits.
Initially, let's consider the case of Sync in a read-only app. This behavior also occurs during initial launch of any app and in any app that has the Sync-On-Start option enabled.
- Read Step 1: When the app on the mobile device starts a sync, it requests data from the AppSheet Server. The AppSheet Server in turn requests data from the Cloud Provider.
- Read Step 2: Once the AppSheet Server receives the data from the Cloud Provider, it computes any virtual columns defined and then returns the data to the app on the mobile device. The app stores the data on the mobile device.
Note that in step 1, the AppSheet Server may have to fetch many tables from the same or different Cloud Providers. In step 2, all the data is sent together to the app. Later, we will discuss various optimizations that can reduce the data transferred and the time taken in these steps.
Now let's consider the case of an app that has made some data changes (to its local data copy) and is then invoking Sync. The app first sends each of the data updates in order to the AppSheet Server (steps 3 and 4 in the figure) and after that, it performs a read-only sync.
- Write Step 3: the app sends each of the added, updated, and deleted data rows to the AppSheet Server. If you captured photos, drawings, or signatures, they are sent along with the data rows.
- Write Step 4: When the AppSheet Server receives this information, it writes the data back to the Cloud Provider. If there are any automations defined, they are also run (these will typically require that the updated rows be re-read from the Cloud Provider before executing the action).
As an app creator, you can improve the speed and performance of Sync. See:
It might also be useful to review this community article: Obstacles with AppSheet for business due to large data
In addition to optimizing sync performance, there are other key concepts and technical features to be familiar with when designing a performant app that can scale to many concurrent users:
- Format rules - Define rules to customize icons, text, and more. Format rules may require a lot of computation each time a screen is scrolled which directly affects interactivity.
- Virtual columns - Automatically compute columns using an app expression. Since these are computed for each column and row and the expressions can be complex, there is usually a significant cost in performance if an app has a very high number of virtual columns. In general, it's best to minimize the number of virtual columns if possible.
- References - Data references are very useful for most applications for usability and to avoid data replication. However they can have a negative impact on performance if they are used excessively because each reference requires a virtual column and additional reads of the referenced data. Consider whether a reference is truly needed in the application before adding them.
- Data partitioning - Partitioning (dividing) your app’s data into multiple tables can have a positive effect on overall performance, regardless of backend data source type.
- Security filters - Provide a way to limit either user access or in general the amount of data being fetched from the back end data source. This can usually have positive effects on overall performance, but special considerations must be taken depending on the back end technology used:
- Google Sheets - Apps with Sheets fetch the entire Sheet data and apply a filter on the server. If dealing with large amounts of data the performance impact could be negative because the security filter executes on a row-by-row basis.
- Cloud SQL - Security filters will be converted into a SQL statement and sent to the database. This can usually improve the performance, but if the expression is very complex the performance gains could be negligible because AppSheet converts whatever it can on the database and then the filter is (re)applied at the server.
- AppSheet databases - Security filters on AppSheet databases should typically improve the performance.
- Offline data and image caching - When dealing with large datasets, it’s also helpful to consider that offline caching can be affected, and even cause, the client device to crash if the device is limited on storage and memory.
In general the AppSheet platform dynamically scales horizontally to serve any size audience. Although the Google infrastructure on which AppSheet runs is equipped to handle extremely high levels of concurrent requests, including millions, the number of concurrent users that AppSheet can support is strictly governed by the scalability of the data source used in the app.
As an analogy, imagine a restaurant that could hypothetically allow unlimited patrons into the waiting area (AppSheet platform), but the kitchen and server staff and other resources, such as dining tables, and how much food available to cook (data source), is what dictates the overall restaurant capacity (that is, how many concurrent customers can be served at one time).
The following table provides performance considerations for the most common AppSheet data sources.
App performance considerations
AppSheet apps with an AppSheet database data source are:
AppSheet databases combine the familiarity of editing data in a table-like structure with the performance and scale of a cloud database.
AppSheet applications using Google Sheets as a data source is very common and practical, especially for small to medium size applications. App creators performing large scale app deployments with Sheets data sources, however, should be mindful of how AppSheet uses the Sheets API so as not run into quota exhaustion issues. Here are some key details to note regarding quota usage:
Again, quota overruns are typically not seen with small to medium size Sheets-based applications, but application creators should take note of these API quota considerations when planning large scale deployments to serve many users simultaneously at peak times.
Example apps and expected Sheets API quotas
A Travel Approval app - A basic app for submitting travel approval requests.
A Daily Tasks app - An app to track multiple daily tasks for employees every morning.
AppSheet apps with Google Sheets data sources can experience concurrency or quota issues with the Sheets API if app access time (avoidance of peak usage spikes), app access mode, and overall data volume are not considered when designing AppSheet with Sheets architectures.
AppSheet apps with Cloud SQL data sources will not experience the same quota limit issues as the Sheets API since Cloud SQL does not even impose any queries per second (QPS) limits.
If a Cloud SQL instance is receiving excessively high amounts of requests for what it is configured for, the database will experience slowness and timeouts due to resource exhaustion (which will appear in the audit logs). Again, this can be remedied by properly configuring the SQL instance for the additional traffic.
Additionally, the types of queries that the app is making against the database and overall data profile (that is, the number of tables, rows columns and whether database views are used) can also impact performance and scalability.
The primary factor however that governs scalability is the Cloud SQL instance configuration, including the following:
The default production and development configurations for the three database types (MySQL, PostgreSQL, and SQL Server) are shown below.
AppSheet apps with Google Cloud SQL data sources will not experience concurrency issues due to quota overruns. Instead, maximum database performance is 100% governed by how your instances are configured. In a production environment with changing needs over time, it’s recommended that you continuously adjust your SQL instance configurations to best suit your performance and concurrency goals.