Relationship Speed GSheet vs Glide

Is there a benefit to build relations with glide computed columns if possible?

I.e.

You have two GSheets of names from using =Unique and build a relation from Name to Name.
In the same scenario you add Glide Computed RowId’s and build your relation from RowID to RowID instead.

Which path do you choose…

  • :man_shrugging:‏‏‎ ‎Doesn’t matter
  • :zap:‏‏‎ ‎RowID is faster

0 voters

Rowid isn’t really a computed column, so the data in that column is always there, just like name would be. It’s pretty much a basic column.

I would say however, that there could be a slight delay with other computed columns, such as a template, lookup, math, single value, etc. Those columns are computed directly on the user’s device when the data is first cached. Once it’s loaded initially, then I would say that computed column based relations are just as fast as basic columns. That is, until the app is closed and reopened again. So the answer kind of depends. I’d say in the case of your poll, both are correct and the fastest, but that’s really comparing basic column to basic column instead of comparing to a true computed column.

I have an app with relations based on computed columns and the sheet it relates to has 4000 rows. That sheet also has a lot of calculations that it performs. It’s really slow to open that part of the app initially because I believe it has to first perform all calculations in the sheet before it can completely link up the relation.

Also be careful with using unique. You are probably fine with what you are doing, but if a row in the original sheet is deleted, it can reshuffle your UNIQUE sheet, which I have seen cause issues in the past. Also, any new name would cause a much more substantial delay as your app would have to wait for the data to get from the app to the glide servers, to the google sheet, recalc the unique formula, then sync back to glide, and finally back to the app…as compared to having everything calculate directly inside of the app. If your unique sheet is doing nothing more than holding a list of unique names, then I would instead recommend the following:

4 Likes

I’m not voting, because I would choose none of the above.

Given your scenario however, I would (and do) always use Glide computed columns to build the unique key set for the relation. This choice has nothing to do with speed, but is because I have a personal mantra of never using spreadsheet formulas if the same thing can be done with Glide computed columns. Doesn’t mean I never use spreadsheet formulas (I do), I just use them very sparingly. And this is not a situation where I would use them.

2 Likes

Thank you both. I actually mis spoke in my original post and I’m just using a simple =arrayformula to build the additional list of names.

It starts with a list of names that is populated with a form from glide so all those created columns are basic columns. I need that same list in several other parts of the app so I bring it in using Arrayformula where needed.

The reason I don’t add names to all sheets at once is when I started using Glide that simply was not possible and there was no delete row either. I started to integrate a bit of appscript to delete rows in parts of the app that doesn’t connect to Glide, thanks @Darren_Murphy. Glide action Delete Row + Darren app script delete row may open the door for me to use multiple add rows in one form and then delete what’s needed I suppose :thinking:.

I figured out some pretty cool stuff and I’m building my =Unique parts of the app with some logic followed by an add row to GSheet all in Integromat. Unable to use glide for this because 50k+ rows in source sheet. Now I end up with Basic Columns instead of formula, eliminates any row ordering issues and cuts down on processing as you mentioned @Jeff_Hager

So back to the original question… would it be better to use a RowID vs =Arrayformula for a relation? Probably the answer is still the same and I need to rebuild the apps lists of names using add rows from forms to realize any gain?

… Do I understand correctly there won’t be any benefit to using Row ID over =arrayformula For the relation?

Always a fan of removing spreadsheet formulas :sunglasses:

I used to have logic like that. I actually had a sheet with names and whenever a new name was added, a script would take over and write the new name to other sheets as well. That way I would have a row to related to in the second sheet and it would be ready to populate with values. The only downside is that a misspelled name that was corrected later would cause a brand new record to be written to the other sheets. It wasn’t my favorite logic, but it was all we had when glide was young. Later, when row id’s became available, I switched to writing the row id to the other sheets because it’s static and can never change. That way a name can be changed without affecting the relations. The whole reason I used a script is because I didn’t trust a sheet formula to reliably keep the same name order. The script only looked for new names and added them to the bottom of the additional sheets.
My additional sheets had other column values, so I couldn’t risk a formula reshuffling the names and misaligning the names to the data. (Kind of a backwards situation from yours.) But, that’s why I don’t trust an arrayformula or unique formula to populate another sheet, unless ALL of the other data in that sheet is sourced from a formula or a computed column. Since then I completely redid the logic. Three additional sheets became one, and now all content is stored by row instead of several columns for each name. Yes it uses more rows, but it’s a whole lot easier to manage and doesn’t use any sheet formulas or scripting.

TLDR: I use Row ID’s to link data, and only write rows in other sheets as needed.

Like I said, RowID is technically a basic column value, so it should be as quick as any other basic column value since it’s not a computed column that has to compute each time it’s synced and cached on the device. Using any sheet formulas to populate a column that’s used for a relation will cause a slight delay for that formula or script to run and resync back to glide. Once it’s populated and synced back to glide, then it’s still treated like a basic column value in glide. The delay would only be noticed when adding a new name. If the names already exist in the data, then there shouldn’t be any noticeable difference.

2 Likes

For me the Arrayformula works because the rest of those tabs/columns are vlookups or computed columns like you mentioned.

Unfortunately I wouldn’t be able to use the RowID in place of my list of names because again I use Vlookpus on data not connected to Glide. What I could do is add a RowID in addition to the Arrayformula and build the relations that stay in Glide using that RowID or to rebuild completely… but I’m not sure it’s worth the effort.

Thank you for your time and comments.

1 Like

But aren’t those formula built sheets pulling data from the original sheet that would have a row ID…and if so, couldn’t you just pull across the row id in addition to the name? Maybe I’m not completely following your data flow and the integration with Integromat. Either way, you’ll have the same results using RowID or using name. The only difference is that Row ID is guaranteed to be unique where with name, you could have two people with the same name.

1 Like

I think you actually understand my situation quite well, appreciate the follow up. Names are added through custom form which will not allow for duplicates.

Yea adding the rowID in addition to formula is what I could try… but without any real gain in performance I’ll probably look elsewhere for optimization.

1 Like