Improve the speed of Sync (2024)

Make sure to read and understand the core concepts affecting app performance. If you can focus on improving Sync performance, it will have the greatest positive impact on the end-users of the app.

In this article, we'll focus on read-only Syncs as this affects all apps. There are additional approaches to improve the speed of Syncs that involve data updates, as described in Improvethe speed of Sync with data updates.

To improve the speed and performance of Sync, consider the following methods:

1. Choose an efficient data provider
2. Reduce the amount of data transferred during Sync
3. Reduce the amount of computation (for spreadsheet formulas and virtual columns) performed during Sync
4. Fetch or compute data in parallel (relevant if there are many tables in the app or many expensive virtual columns)
5. Use a faster network (this is relevant for the connection between the device and the AppSheet Server)
6. Run Sync in the background so that the user is not waiting for a Sync to occur

The AppSheet platform provides tools (like the Performance Profile) for you to understand, analyze, and improve app performance.

1. Choose an efficient data provider

AppSheet supports many different data providers. All the data providers vary in terms of the initial latency in responding to a request. Here are some general rules of thumb:

• Database providers are generally better than file system or spreadsheet providers.
• Among spreadsheet file systems, Google Sheets is the most performant as it has been built from the ground-up to support web-based concurrent data access.
• If using Excel, it is more efficient to use Office365 than a pure file system like Dropbox or Box. With Office365, spreadsheet formulas can be run in the cloud by Office365, whereas with Dropbox and Box AppSheet has to download the entire Excel file and evaluate formulas locally.

In reality, this may not be a choice you have because your data set may already reside in a specific data provider.

2. Reduce the amount of data transferred

Remove unnecessary data

The first step is to remove unnecessary data in the table/worksheet itself.

• Delete empty rows, including empty rows at the bottom of the sheet.
  If your worksheets contain hundreds or thousands of empty rows, you can greatly improve performance by deleting them.
• Delete empty columns, including empty columns to the right of the sheet.
  If your worksheets contain empty columns, you can greatly improve performance by deleting them.

A spreadsheet workbook (that is, file) contains one or more worksheets. The next step is to ensure that there isn't unnecessary data in the spreadsheet workbook.

• Only include worksheets that the application requires and remove the rest.
  Extra worksheets will add overhead when the data provider is fetching the requested data. Any changes to those worksheets also interferes with caching optimizations.

• Place read-only tables in their own workbook.
  There are greater opportunities to cache read-only tables. When placed in their own workbook, the platform can more accurately track if they have been updated.

Configure AppSheet settings

Various settings in your AppSheet app can reduce the amount of data transferred. The app may explicitly reduce the amount of data via:

• Security filters
  Use security filters to eliminate rows that do not satisfy the filter criteria. If the data comes from a spreadsheet source, the entire spreadsheet is read by the AppSheet Server, the security filter is applied, and the subset of rows that satisfy the condition are sent on to the app. If the data comes from a database source and the filter is simple (such as [ColumnX] = USEREMAIL()), then the filter can be "pushed" into the data retrieval from the data source. In other words, the data retrieval from the data source also becomes much more efficient and less data is transferred to the AppSheet provider. See Scale using security filters.

• Partitioning
  Manage very large data sets by dividing them into partitions. Each user only ever needs the data in one partition. Naturally, this significantly reduces the amount of data transferred. See Scale using data partitions.

The app may also implicitly reduce the amount of data transferred by avoiding redundant data transfer. Data transfer is redundant if the app already has a cached copy of the latest data available (from a previous sync). In order for caching to be effective, the system needs to record when a cached copy was maintained and check if the data source has been updated since then.

To configure the data caching settings:

1. Open the app in the app editor.
2. Select Settings > Performance.
3. Configure the settings under Sync: Cloud to Data Source.
4. When you are done, save the app by selecting one of the following:
   • Save - Save the app.
   • Save & verify data - Save the app and verify that it is runnable based on external dependencies.

Configure the data caching settings:

3. Reduce the amount of computation

Computations happen during sync at two places:

• In spreadsheet formulas
• In AppSheet virtual columns

Both can add significant overhead to Sync times.

Spreadsheets use formulas to compute data. Some formulas can be very expensive and long-running. Such formulas can cause significant delays and in extreme cases even timeouts when using AppSheet.

1. Ensure that the spreadsheet only uses simple/cheap formulas.
2. Cross-sheet formulas and those using external services (like Google Finance) are likely to severely impact sync performance.
3. It is particularly important to minimize worksheet formulas when the data source is Excel on Dropbox or Box, because these formulas must be re-computed by the AppSheet Server each time the Excel worksheet is read.

Virtual columns in a table are defined by App Formulas and are computed by the AppSheet Server on every sync. App Formulas are very powerful and expressive, but it is possible to write very inefficient App Formulas. For example, in an Order Capture app with three tables (Products, Customers, and Orders), let us assume there is a virtual column defined on the Order table to find the sum of all prior Orders made by the same Customer for the same Product. Depending on the amount of data in the app, this will probably be very expensive to compute against the entire data set. The Performance Profilewill indicate if virtual columns are contributing significantly to sync time.

4. Fetch or compute data in parallel

So far, we have ignored the fact that most apps have multiple tables. Let's say your app has five tables. Are they fetched from the data source one after the other or are they fetched in parallel? Ideally, this would be as fully parallel as possible, but doing this is resource-intensive for the AppSheet server (each parallel request requires a concurrent thread of computation in the server and there is a limit on the total number of threads that can run at the same time). Consequently, we control the degree of parallelism based on the subscription plan of the app owner --- the more expensive plans are given a greater degree of parallelism.

5. Use a faster network

Most people intuitively believe that a faster network should lead to a faster Sync. The same intuition also leads people to believe that the biggest bottleneck in sync is the speed of the data network (2G vs LTE for example) used by the mobile devices. In most situations however, this is an oversimplification.

In our observation:

1. Cloud provider latency is usually the biggest contributor to slow syncs. The actual data transfer between cloud provider and AppSheet server is pretty efficient (the internet has high throughput).
2. Network latency between the device and the AppSheet server can also add significant delay. We observe this typically with customers who are geographically remote from the AppSheet server cloud.
3. The actual data transfer from the AppSheet Server to the mobile device is usually very efficient (that is, even though mobile networks may not have great throughput, it is more than sufficient for the data volumes being transferred). The fact that AppSheet compresses the data before transmission helps with this.

This is a very important observation and it is because AppSheet apps typically have relatively small data volumes. Even if the underlying data set is very large, each app user typically only needs a small subset of the data and this is achieved via mechanisms like Security Filters and Data Partitioning.

6. Run Sync in the background

• With all Sync options disabled, the default behavior of the app is to sync immediately if a row is added/updated/deleted by the app.
• Enable Sync on start to force the app to sync every time it is restarted. This is a good mechanism to limit the staleness of the data.
• Enable Delayed sync to queue updates rather than sync them immediately. For apps that are used in remote regions (such as locations with poor internet service), this is a good option. When the user returns an area with good network access, such as the office, they can explicitly click Sync to sync all queued updates to the backend. Note that the longer the time between syncs, the greater the possibility for data conflicts because someone else may have updated the same data.
• Enable Automatic updates to hide latency without data staleness. This option is appropriate for environments where there is good network connectivity. Any updates made by the app immediately kick off a sync in the background, while the user can continue to use the app. If there are no updates made by the app, the system will still periodically run a sync to ensure that it has the latest data. This option will increase use of battery, network, and server resource and likely run more syncs than are necessary. This will need to be weighed against the benefit of having an app that is always close to up-to-date without users waiting for sync.