This question is for gurus so we have a table with 10K rows, which is better to put them all in one table or split them into 5000 into 2 tables and connect them with the relation?
My question is with regards to the performance of the application when it loads
UPDATE: We are using only Glide Tables
I generally try to avoid splitting a single table into multiple tables. The only time I have done it is when the data is highly transactional - eg. many new rows are being added on a daily basis.
In such cases, what I have done is split it into 2 (and sometimes 3) tables.
The first table will contain the newest records - maybe the past 7 days of data.
The second table will contain data going back maybe another 30 days.
And the third table will be an archive of all the older data, and I generally don’t use this table in the app.
The problem with splitting a single table into multiple tables is that whenever you have to produce a report/chart/export that needs data from from two or more of those tables it gets quite tricky. A simple relation isn’t good enough. Essentially you need to filter the records that you need from each table and then temporarily join them together in a separate working table. I’ve done it (in Glide Tables), but it’s not a simple exercise.
is there an automated way to move data from one table to another based on date, or will this need to be moved manually?
Not an automated way, unless you use an external process like Make or Google Scripts.
I have a slightly complicated process in my app. Rows that are more than 2 years old have an IF column that indicates they are ready to be archived. I then have a single relation that retrieves the first accessible row that is marked for archive. When the user does certain things in the app, a custom action overwrites their email through the single relation with the word ‘ARCHIVE’ tagged on the the email. This breaks Row Owners, so that old row is not longer accessible. In my Google Sheet, I then have a script that spins through that table around 1AM in the morning, first moves any row with ‘ARCHIVE’ in the email column to another table and replaces ‘ARCHIVE’ with ‘DELETE’. Then I spin through and delete any row with ‘DELETE’ in the email column.
So old rows are slowly set to archive without using too many extra updates. The script does all of the moving of the data on the back end.
So long story short, you have two options. Build a custom action that does an Add Row and. Delete Row whenever a user does something in the app. Or build an external process to move rows on a schedule.