App performance: Core concepts

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

Latency versus throughput

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. 

Improve the speed of sync

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:

• Improve the speed of Sync
• Improve the speed of Sync with data updates

It might also be useful to review this community article: Obstacles with AppSheet for business due to large data

Improve app performance

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.

Performance considerations for common data sources

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.