Split up a table with many columns into 2 tables with less columns, same rows, for speed improvement

[Ricky_Chua]

I have a table in gSheet with 20,000 rows at 40 columns. This particular table seems to be slowing down my AppSheet app.

Will I have any speed improvements by splitting into 2 tables. Both tables will have the same ID column and values, but one table has 20 columns and the other has the remaining 20 columns?

For more context, i do require my view in AppSheet to show all 40 columns, which means I will still be creating 20 virtual columns in the first table. Do the presence of the virtual columns still mean that the 2nd table will still be accessed constantly to display those values, hence increasing complexity without reducing speed? 

[Suvrutt_Gurjar]

1. You may want to analyze, if the app really needs all the 20000 rows of that table to be always available to the app user. Maybe some older dated records are not required.

I believe rather than columns to be split, a better and AppSheet recommended approach will be to use appropriate security filters to limit the number of rows loaded into app user's device for an app's tables,

However security filters only help up to an extent if you are using spreadsheet as a database. With SQL databases , properly designed security filters work more effectively.

Please take a look at the following help article and many other associated articles referenced in that article.

Security filters: The Essentials - AppSheet Help

2. In general, 20000 rows  and 40 columns in itself are not  such a high number of rows/columns. So, you may also want to check if there are any sync time consuming virtual columns in the table.  If there are such virtual columns, you may want to decide how you can avoid them without compromising the user experience.

[Ricky_Chua]

I experimented with Security Filters, and found that Data Sync will be turned off. Delta sync has a noticeable effect on my App speed because I will not be filtering out that many rows. 

As for SQL, I found that I need to switch to the Enterprise plan. The AppSheet representative that gave me a quote took multiple days to reply each message. The price was also multiples more than Core plan. 

[Suvrutt_Gurjar]

Okay, got it. Thank you. Well, I think you will need to apply a "mix and match" approach within the available options. if you are not filtering out many rows and the said table does not change very often , then yes the delta sync makes more sense than the security filters.

---

@Ricky_Chua wrote:

This particular table seems to be slowing down my AppSheet app.

---

Have you been able to take a look at the performance monitor for a more definitive understanding of where the sync time is getting consumed.

Regarding splitting the table by columns and still loading all the 20000 rows for both the split tables, I believe it may not be much beneficial for app performance.  

[MultiTech]

As @Suvrutt_Gurjar mentioned: 20k records with 40 columns isn't really that much. 

For reference, I've got an EMR app that has 5 main data point tables that we regularly keep under 100k records (hosted though a GCP SQL table); that particular app also has 6 tables, hosted in Google Sheets, all of which have upwards of 10k+ records each - as well as 20 or so additional supporting tables to help make everything work.  All of these deeply interconnected, with an unbelievable amount of virtualization.

• The app takes on average 15 - 25 seconds for the initial loading, and subsequent syncs take approx. 8 - 15

The key is how you handle your virtual columns, formatting rules, and show if rules; as long as these are appropriately made, you can build incredibly complex systems - without having to wait 60+ seconds for your app to open.

• Avoid brute force SELECT()
• References are your friend; essential actually
• Using slices to create data subsets
• Security filters to limit records that don't need to be there
• and more

There are a lot of ways in which you can build your app efficiently, a lot of best practices that have been discovered over the years.  You can find them here in the community, if you dig deep enough long enough, or you could ask Appster. 😉 

Hope it helps!