Fixing load times from large sheets

Hello Everyone! I am nearly done with my app walkbysight.com (https://walk-by-sight.glideapp.io/). It is coming along nicely and I am very proud of it. I have a problem with the app taking a very long time to load the main part of the app where you scroll through topics. I believe it is because of the large amount of Bible Verses ( 31104+) I have in Google Sheets that are linked through a relation. I have thought of two options.

1: Use an API to call the verses and allow users to search. I already got permission from BibleApi to use theirs, but I can’t figure out how to make it work. I could use another bible API if it is easier to make. I have followed and copied this guide (Using App Script with a JSON API) but it doesn’t translate one to one to using the BibleApi in its place.

2: Others have said that for large datasets that don’t change often to import them into glide tables. That’s no problem if I could figure out a way to import them. I already tried this (Import Data From Excel & Sheets into Glide Table - #18 by Manu.n) But clicking over 31104+ times is not a viable option.

Or maybe I’m just wrong altogether about the long load times, most of my other sheets are not super big and I have few images. Any ideas guys?

Though I haven’t tried it this seems to be the recommended approach for your situation.

FYI you can copy and paste right from GSheet into a Glide Table now but DO NOT do it with that many rows. Copy paste may be useful for you in another table that has a small amount of rows in it. There is no undue in a Glide Sheet so be careful/ try in duplicate app first.

1 Like

Thanks, I’ll try the robot and get back to you.

I did try coping and pasting but you have to add the rows you want to paste.

So If I copy 10 rows from Google sheets and paste it into a glide table with only 5 blank rows, only the first five rows will copy. So I’m back to the problem of having to click a gazillion times.

1 Like

For 10 rows you could click add new row in the bottom left until you have enough rows. Even if you clicked a gazillion times and had enough empty rows ready it can not handle a copy paste of that many rows. Only good for small tables.

Good luck!

Edit: When you see @Jeff_Hager’s speech bubbles you be like…

image

I’m in a similar situation and have been trying to find better ways to make things more efficient and faster. I have a large-ish table with a handful of relations linking to other tables along with some several computations…and relations in those other tables linking back to the large table.

One thing I’ve learned is that any relations that rely on computed columns, such as templates, will take longer to build initially. In my case, I did have one particular column that was built from a vlookup in my google sheet and used for relations. I found a way to reproduce the value using glide computed columns, but it actually made my app quite a bit slower because it first had to run all of the calculations to build the values that could then be used to build the relations. I ended up reverting back to using formulas in the google sheet for this particular case, since glide only sees it as static value instead of a value that needs to first be computed. That helped bring the speed back to where it was.

I also think any other computed columns in a large table can also cause slowness, even if they aren’t used to build the relation. I think the first time that table is accessed, all of the computed columns need to be processed before any data in that table can be shown. Especially if you have over 31k rows, that becomes a lot of data to process. I also think that whenever you have relations, all computations in those related tables need to be processed before the table data is shown.

Overall, I don’t think migrating your data from a google sheet to a glide table is going to make a whole lot of difference, especially if it’s static data. All you are doing is eliminating the extra travel time between glide servers and the google sheet. Glide already has a copy of that data on their server and the app is directly communicating with the glide server. If the data is static and isn’t changing via the google sheet or the app, then there is nothing to sync, so it’s not really going to speed anything up.

Hard to say what you can do to make it better. Wish I could be of more help. Sounds like you are working with a lot of data. For me your app loads quickly. Clicking on the main topics is quick, but the sub-topics take a while to load initially. The controversial topics seem to load slowly too, so I’m not sure if those are set up differently than the main topics on the front screen.

Mainly it becomes a lot of trial and error to eliminate as many computed columns as possible on any large tables, and reduce or simplify any relations.

One thing that might also help is if you can maybe split the Verses table into separate books? Just so the entire verses table isn’t trying to load all at once. Separating it might stagger the loading or computations, so it would appear to run faster.

I could probably offer other ideas, like setting a user specific value when you click on a topic, which would then be used to build a relation to the sub-topics or verses. So you are only creating one active relation at a time. But I don’t know if that would actually improve anything, and the added complexity could actually slow things down.

Short of trying to implement an API, I don’t have any good proven ideas to help you out, but maybe I’ve given you a few things to think about and try.

6 Likes

I think this is a very important point. In the past, I’ve used lots of complex relations that were dynamically built using templates. I used to think I was quite clever in being able to to this, but in recent times I’ve come to the realisation that I probably outsmarted myself.

More and more now I find myself abandoning the idea of building complex relations to generate data for inline lists, and instead I’ll use logic in the source table to “pre-filter” the list, and then use the whole table as a source to an Inline List component (as opposed to a relation column), with the “pre-filter” as a filter condition.

It’s generally a simpler approach, results in fewer computed columns, and definitely appears to perform better.

6 Likes

Totally agree. I think I’d still attempt complex computed relations as long as I’m working with small sets of data, but it seems to add to loading time.

It’s things like this where I wish we had a little more insight on the internal workings of glide. How does stuff load? In what order? Is it single or multi threaded? I think it would help a lot to more efficiently layout data and know some best practices to avoid slowdowns.

3 Likes

@mark would be nice if you could give @Jeff_Hager and @Darren_Murphy some input here - so they can help the rest of us :stuck_out_tongue_winking_eye:

1 Like

Great Idea about using Google Sheet to do simple formulas for things, I’m going to take a look at my app and see what I can easily reproduce in Google Sheets. I forgot that Glide stores the file on their server, so moving the data over doesn’t help. I only really have the “Verses” sheet that is huge, everything else is in the hundreds at most.

“Hard to say what you can do to make it better. Wish I could be of more help. Sounds like you are working with a lot of data. For me your app loads quickly. Clicking on the main topics is quick, but the sub-topics take a while to load initially. The controversial topics seem to load slowly too, so I’m not sure if those are set up differently than the main topics on the front screen.”

These are all “Sub-Topics” that have the relation to the verses sheet. If a subtopic is linked to “Revalation” the verses sheet will have to be loaded in its entirety, I think that’s the bulk of the problem. I was talking to my Wife as she contributes to the app development. I’m not convinced that topics need to be linked to verse. It may be better to just link the Bible books to the topics instead.

“One thing that might also help is if you can maybe split the Verses table into separate books? Just so the entire verses table isn’t trying to load all at once. Separating it might stagger the loading or computations, so it would appear to run faster.”

That’s a good idea as well, maybe a combination of everything will fix the problem.

1 Like

I have only 3 tables that make up all of my submissions for this reason. I just use filters based on the topic or subtopic it is to sort the app vs have many tables linked.

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.