When an app starts and fetches data, there are three steps involved:
- Cloud to AppSheet server: the data has to be fetched from the cloud (a spreadsheet or a database or API) to the AppSheet server
- AppSheet server to the app: the data has to be sent from the AppSheet server to the app running in a browser or device.
- Persist the data locally on the device/browser. AppSheet apps always download their entire working data set to the device or browser. This allows the apps to work seamlessly when offline, partially disconnected, or on a slow network.
App creators should consider how the app will scale when the data sets become large. The primary motivation is performance, of course. However, there are other motivations as well. For example, Smartsheet does not allow more than 5000 rows in a single spreadsheet. Google Sheets limits a spreadsheet to 2 million cells. Apps may need to scale to go past these limits.
There are two kinds of app patterns that can lead to large data sets:
- Partitionable: Such apps may have a lot of data rows, but each app user only accesses a smaller subset of the rows. For example, an app that maintains timesheets for employees. Each employee only has one timesheet entry per day, but across a large organization, there may be several tens of thousands of employees.
- Not Partitionable: Such apps require each user to have access to a large data set. For example, an app that keeps a catalog of a million products and wants this entire data set accessible in the mobile app.
Scaling Partitionable Apps
AppSheet apps can scale very effectively for the first category (apps suitable for per-user partitioning). This section identifies three mechanisms that are used to achieve scale. With these techniques, a properly designed app can even work against data sets that have millions of records.
- Scale using security filters: the data is filtered after step 1 (Cloud to AppSheet server). For spreadsheet data sources, the entire sheet is fetched to AppSheet and the filtering ony reduces the data in steps 2 and 3. For database data sources with simple security filters, the data is filtered during step 1 (Cloud to AppSheer server) at the database. This can lead to very significant performance improvements.
- Scale using data partitioning: the data is divided into many identical partitions, each with a different subset of the data. Each user gets data from just one specific partition. This technique can be used along with security filters and leads to almost infinite scalability.
Each of these techniques is described in a separate article in this section. The standard recommendations to scale an app are:
- Add security filters (requires user signin or user settings enabled in the app)
- Data partitioning -- this lets you scale your app while remaining on spreadsheets.
- Move to a database data source -- refer to the data sources document for information about databases (MySQL, SQL server, etc).
Scaling Non-Partitionable Apps
AppSheet apps do not work effectively for the second category (apps not suitable for per-user partitioning). If a lot of data must be available to every user of the app, all of it has to be fetched and moved through all three steps. This will certainly lead to a slow app that becomes unusably slow as the data set scales.