I currently have Attendance table with 45 columns that grows by approximately 15,600 rows per year (702,000 cells/year). I am considering splitting it into two tables:
Check-ins: 18 columns (62,400 rows per year approx.) (1,123,200 cells/year)
Schedule: 35 columns (15,600 rows per year approx.) (546,000 cells/year)
Possibly increasing by 2–3 additional columns each table.
Attendance table disappears.
With this new structure, I would need to update certain columns in Schedule based on data from Check-ins, running and action by a bot. The two tables would be related through a Ref column.
This change would improve the UX for employees. Instead of scrolling through a list to find their name and then entering their code (as they currently do), they would only need to enter their code (not scroll) in a form.
My question is: In my case, is it better to have two tables instead of a single large table? Would it make a noticeabledifference regarding sync times?
I am sure, other expert community colleagues will also share their insights.
My 2 cents (but long response : )
I believe it may not be possible to respond to your question in black and white or pure yes and no.
First, 15K rows are not an extraordinarily high number of records, some app creators report having much larger tables.
In general, you may want to evaluate the following:
Are all those 15 K + rows are always required by the app user? The previous months’ attendance records may not be required all the time to be loaded in the app. Also I am sure each app user may not need all those 15 K+ rows to see every day.
In fact only managers or admins may need to see more rows and many other users may need to see only their own attendance or rows.
So you may want to take a closer look at the security filters that load just the required rows as needed by each user in her/his device, thereby reducing the sync time.
Even for power users ( admins and managers) who need to see more rows, you could use UserSettings option to allow them to dynamically change the security filters settings.
Finally, security filters also are also not miracle cure:
They work much more efficiently with SQL DBs compared to spreadsheets. This is so because in case of spreadsheets the entire table is read in the AppSheet server and then selected records are passed on to the user’s device.
Even security filter expressions need to be designed so as to keep them simpler as possible.
Additional reference reading you may want to do
Finally, apart from just the number of rows, you may want to also evaluate, if sync expensive virtual columns are impacting the sync time. Again, all virtual columns no necessarily adversely impact the sync time. Those with multirow expressions such as SELECT() , MAXROW() etc. are the one first to look at.
: )