@Sean_Lim Hi Sean, less than 2 seconds to read more than 44,000 rows in Google sheets is actually not bad. If you migrate to a SQL database, there might be some small performance improvement, but you’ll need to take into consideration the additional cost of using SQL.
Based on your description of the problem, I believe the real bottleneck is the time taken to compute virtual columns. You mentioned using the SELECT expression, which can take a very long time to compute. To understand why, consider this scenario.
Suppose you have a table A with 44,000 rows, and this table has a virtual column with a SELECT expression. In the selection condition of the expression, you compare a column in table A to a column in table B. Suppose table B has 21,000 rows (I’m just basing these numbers off the screenshots you shared). Because you need to compare every value in table A against every value in table B, this would result in a total of 44,000 x 21,000 comparisons. If every comparison takes 1 microsecond to be performed, that would result in a total computation time of 924 seconds, or 15.4 minutes.
Of course, the sync time of your app is much smaller than 15 minutes, but you get the idea. Based on this, the biggest improvement in your app’s performance would come from either simplifying your virtual columns, or from reducing the number of rows that are used in these virtual columns. If possible, avoid using expensive expressions like SELECT or LOOKUP with large tables. It’s also a good idea to archive old data that is no longer used. You can do this either by removing old data from your spreadsheets directly, or you can filter data using security filters in AppSheet. For Google sheets, security filters will not reduce the amount of data that our server has to read from the sheets, but they do reduce the amount of data that will be used to compute virtual columns, so they can be a big help. If you use SQL databases, security filters will also reduce the amount of data that our server reads directly from your SQL tables.
I don’t know which plan you’re on, but if you’re on a business plan, you should certainly consider using data partitioning. You can find more information about data partitioning here:
More information about how to scale your app in general can be found here: