I made a timesheet app for daily check-in and check-out of 3,000 employees by storing data on Google Sheets. It was working nicely at the beginning, but after the timesheet table grew beyond 20,000 rows of 26 columns with 14 virtual columns, the apps getting very slow syncing. I did some tweaks already such as turning the catching and delta sync on, it helps but user still have experience on slow sync at around 35 seconds on average.
I then do data partitioning by splitting google sheets worksheet into 64 sheets (tabs) and set condition expression base on user’s department. The app works as expected, it syncs really quick to around 3-5 seconds, but another issue arises, the records adding and updating getting very slow. I am wondering if anyone experience similar issue. Any suggestion would be appreciated.
BTW: I am considering moving data from Google Sheets to cloud database as next attempt.
Do the employees who are adding check in/check out times need to load data for all the previous times they have checked in/out?
I guess that each employee should have a minimum number of records per day which would be Check In - Morning, Check Out - Lunch, Check In - After Lunch, Check Out - End Of Day.
The app would only need to allow Adds and would use a security filter to only load records with a AND([DateofCheck] = TODAY(),[User] = USEREMAIL())
Yes, employee need to see the past check-in/out, however your suggestion on adding date condition to restrict numbers of previous records loaded is great and help. I have added your tweak suggestion and it seems to be quicker to sync, but adding and updating record is improve only a little bit.
Let’s wait for the next crowded check-in/out to evaluate the additional tweaks.
Almost certainly, the performance problem is due to the virtual columns. Look for ways to remove the virtual columns, replace them with normal columns, or find other ways to compute and store their values.
The app that your employees will use to do the checkin/checkout part. This does not need all the complex VCs as it’s a simple data entry app.
Use the second app to analyse the data set from the checkins/outs. It would be expected that this app would be slower to perform, but even here you could set up security filters so that it only loads (for instance) the last month’s worth of data to analyse.