Obstacles with Appsheet for Business Due to Large Data

[JOHNN]

Hi, Team Dev cc to  @Arthur_Rallu @lyzeth.

After using Appsheet for a while, I found some obstacles when using it for business due to the issue of large data. I tested it on our company's actual app and the results were surprising.

Before getting into my testing phase, my current app has 16 tables, all of which are stored in Google Sheets. One table has 82,000 rows of data. I know that spreadsheets in general, and Google Sheets in particular, have limitations, with Google Sheets having a limit of 10 million cells. Therefore, in a business environment, I would need to switch to a different platform such as MySQL or SQL Server to use it.

I input all 82,000 rows of data into a cloud-based MySQL platform and connected it to Appsheet. I compared the speed of loading data between when the data was stored in Google Sheets and when it was stored in Cloud SQL. Note that the settings were the same in both test cases, and my app did not use any virtual columns except for the automatically generated columns when connecting data from two tables. The results were as follows:

Sync speed: When checking the speed of loading data on Cloud MySQL and Google Sheets, the speed of loading data on Google Sheets was much faster than on MySQL. On average, it took between 19 and 26 seconds to load 82,000 rows of data on MySQL, while the speed of loading on Google Sheets was between 2.2 and 4.2 seconds per sync.

The speed of adding and deleting data is also an important factor. When testing both MySQL and Google Sheets with 82,000 rows of data, the speed of deleting and adding new data on both platforms was from 1.1 to 2.74 seconds per row of data, while Google Sheets returned the speed of deleting and adding new data from 1.2 to 2.06 seconds per row of data.

After testing with an app containing about 20 rows of data and stored on Google Sheets, the results were as follows: the loading speed ranged from 0.3 to 0.7 seconds, and the response time for adding and deleting data ranged from 0.7 to 1.1 seconds.

I found a clear difference in the results. I wonder what causes Appsheet to take 19 to 26 seconds to load data. I retested by using the security filter feature on the table to limit the amount of data that can be displayed (instead of displaying all 82,000 rows, I only allowed half of them to be displayed). At this point, the loading speed decreased to 7.5 seconds.

I can guess that during synchronization, AppSheet runs an algorithm to load all data into the browser's cache memory before allowing the user to use it. This would be good if the app data is not too much. But if the app data in the business is too much, say over 100,000 rows, would this still be good? Is there any way to handle this asynchronously so that the app can load only the first 1000 rows, and then automatically load more if the user wants to view them? I'm afraid that using AppSheet for a business with a large amount of data, say for 2-3 years, would result in waiting times of 50-70 seconds each time synchronization occurs, which is unacceptable for a business application. Does AppSheet have any solution for this? 

[WillowMobileSys · Accepted solution]

First, I want to Thank You for taking the time to analyze the performance and then report back to this community with your findings.    This is very insightful and helpful - especially to the newer App Creators.  

There are a few things I'd like to clarify based on my understanding, experience and educated guesses.

---

• The 10 million cell limit is...PER TAB.  That is very large for a single table.
• Google Sheet Sync faster than MySQL DB - I believe there is some connection optimization happening between AppSheet and Google...a good thing for us!
• Google threshold and DB overhead - DB systems have a much larger processing overhead when interacting with data.  It is expected that they will perform slower overall on smaller sets of data.  There is a threshold where Google Sheets WILL start to fall behind and the DB performance will take off.  It is my hunch that 82,000 rows of test data simply hasn't hit that threshold yet.  It would be interesting to repeat these tests with increasingly larger sets of data.
• Businesses with large datasets - It is true, that over several years of an app running, the amount of historical data can grind an AppSheet app to a snail's pace - IF the app were allowed to continue to load ALL data.  It is rarely, RARELY ever the case that a business needs to load 82,000 rows of data for daily use.  Making smart use of app segregation, filtering techniques, scaling schemes and archival processes (all things a business should be considering anyway), an AppSheet app (or system of apps) can likely run indefinitely for large businesses.

One other side factor I'd like to make clear.  The tests presented here are strictly concerning the loading of data from the datasource in the app.  When you look at the performance of a sizeable AppSheet app, there is a relatively small portion of the App Sync time spent on loading of data.  Moving from a sheet based source to a DB can reduce that data loading time but since it's only a small portion anyway, there will be only a slight improvement in the overall Sync times. 

The point is that is far better, when first building an app, to focus on efficient implementations and filtering.  This will keep performance in check and increase the longevity of an app much more than a choice of datasource. 

[Suvrutt_Gurjar]

Well articulated response as usual @WillowMobileSys 

[sergio357]

Entonces xq dicen q es mejor usar bases sql que hojas de cálculo.... Dicen q después de cierto número de filas el cargue de tu app aumenta considerablemente en tiempo 

[WillowMobileSys]

The loading time of your data will ALWAYS increase as the amount of data grows - regardless of which data source you use.

Generally speaking, SQL databases are faster at loading the data into the AppSheet services than with sheets. 

But the BIG advantage of SQL database usage is that you can use Security Filters that, if built correctly, will be used by AppSheet to filter the data directly on the database side. 

For sheets, the data is loaded into the services first and THEN Security Filters apply.  This gets considerably slower as the data grows.

I hope this helps!