In my case, I have around 8k rows in one table that belong to one user. That table is linked through relations to at least 4 or 5 other tables, one of which is around 2k rows. Some of those other tables also link to each other, so it’s a big circle of interlinking tables.
Of the bigger three tables, one contains a list of billing cycles for each coach, another table contains each lesson that was taught by a coach to a student, and the last table is a google sheet query that builds a list of unique coach/billing cycle/student combinations. All of this is to track lessons, and calculate invoices to bill each student per billing cycle, per coach.
The math is simple, but all individual lesson amounts need to calculate before the amounts per coach/billing cycle/student can calculate, and before the totals for the entire coach/billing cycle can calculate. I couldn’t even tell you how many times it’s iterating through that data to come up with those totals, but I’m sure it’s quite a lot.
So, I have a pretty good idea that my problems are self induced knowing how Glide currently works and how I have my data interconnected. I’ve done several tests to see if different methods would be better or not, which has helped me to better understand how glide works and get my app to where it is today. Over the past few years, I’ve done several things to make it better.
- I’ve simplified my logic to reduce as many computed columns as I can.
- I used to have payment logic to track if bills were paid, but my users never used it and it greatly complicated things, so I recently removed it and will rebuild it if needed in the future.
- Since Glide does not have conditional relations, I had to resort to building keys using google sheet logic so I would have a value to use for relations between tables. I can reproduce it using glide logic, but it got much to complicated and slowed everything down terribly. I usually don’t suggest using google sheets for any computations, but if timing doesn’t matter, it can be a good way to offload some logic outside of glide. When I had the payment stuff, I even went as far as creating a separate google sheet to offload some calculations from the primary google sheet since it was taking minutes to process and would cause weird issues with lost data within glide due to google syncing with glide while google was to still processing. Since I didn’t need instant results, offloading the computations to one or two other google sheets wasn’t much of an issue, but kind of a pain to maintain if I started deleting columns for moving columns around.
- Recently I finally applied row owners, which makes it 100% better for those that don’t track lessons, but still slow for those that do. With this I’ve rebuilt the lesson entry from a native form to a custom form. I did this for a couple of reasons, but mainly I built a series of relations and actions that will find the earliest lesson and billing cycle that belongs to a coach, but is earlier that the most recent 2 years. Through those relations, I will append ARCHIVE to the row owner email. This will make the rows unowned to the coach but still in the table. For each lesson that a coach enters, potentially two old lessons and two old billing cycle records will be archived and permanently prevented from being downloaded to the user’s device. If a billing cycle still has lessons attached to it, it will not archive until all related lessons are archived. The results won’t be immediate, but over time it will reduce the number of rows that are downloaded and the app will get faster. Eventually the number of lessons and billing cycles will will become a much more manageable number and hover at around 4k rows that are downloaded and processed, even though the underlying table will continue to grow.
- Once my app converts to the new computation model, I’m also hoping that I will see improvements in speed. Fingers crossed.
It’s been an ongoing process to simplify things as new ideas pop in my head, I learn more, and as new features are released by glide. This whole thing started before glide had any computed columns or even a data editor, so all computations logic had to be in the google sheet. Over time, I’ve moved logic to glide and made it better and more efficient. I’ll never be done making improvements. I think the archiving functionality will give me the best results in the long run. I want to keep the data, but there is no reason that the app needs to load and process that old data anymore.
Overall, it’s really hard to advise on best methods because it can largely depend on each and every situation. All I can say is to keep things as simple as possible and just try different things. Have an open mind to try something regardless of how outlandish it may seem. It just might work. And I’m sure glide will keep improving. It’s already light years ahead of where it was three years ago.
