Hi All –
I have big performance issues and trying to optimize my Relations in Glide Tables.
I have a Query field in a bigger table that holds a list of jobs that acts a relationship with all of those jobs’ associated equipment in a smaller table. I wanted to either eliminate the Query field or put the field in the smaller table.
The problem is that it is a many-to-many relationship. One job can have many pieces of equipment, and one piece of equipment can be associated to many jobs. So the “is included in” feature of the Query seems necessary.
In a many-to-many relationship is there any way to replace a Query field with a normal Relation field? Can’t seem to figure it out.
Thank you!
You could use a link table with something like the following:
Then add one row per job, per equipment piece.
3 Likes
I think this would work but unsure how it could be sustainably maintained. Equipment associated to jobs sometimes changes and vice versa – add, delete and modify actions would be needed to maintain the association table which might be a bit resource heavy as the delete actions I think could only be done via webhooks.
You can delete multiple rows via a query/relation, as long as it’s not a big table.
If it’s a big table, you could offload the delete to a server-side workflow, and either use the API or a workflow loop.
1 Like
What I’ve done is build the Query in the smaller table. Then add a lookup column to get a list of Row IDs from the query. Then I finally add a Relation in the bigger table linking the Row ID to the Lookup column in the smaller table.
This helped me a lot with performance.
I do have a question though. How is equipment defined in your jobs table? Is it a comma delimited list? I’m curious why you need to use ‘Is Included In’. If you have a comma delimited list of equipment, then you can split that into an array and use a regular Relation. It sounds to me like you may not need to use a Query.
2 Likes
I never thought of the array solution. The equipment identifiers are indeed comma delimited, so that could indeed work. Will try it out today!
1 Like
I do the something similar. I create a query and then create a joined list of Row IDs for this query which I use in future ‘other’ queries to scope them down. In my case I have a hierarchical relationship where I need to roll up the results of the ‘child’ query to the ‘parent’ to the "grandparent’ etc. The benefit is consistency as well as performance.
Not sure if this helps in your queries/use case.
2 Likes
For my use case, I have a table of lessons (let’s say 10k rows). Then I have a table of billing periods with a start and end date (maybe 100 rows or less). When Query became a thing, it made it possible to use the query to determine which billing period each lesson belonged to because I could use a date range in the query. However, query is much slower than a relation, and doing a query 10k times just killed the app. By moving the query to the smaller table it only needs to run that query 100 times instead of 10k times, and I was able to create a lookup of all lesson row IDs which I could then use to create a relation from the big table to the small table.
4 Likes
I use this technique for most/all many-to-many relationships (got this from either you or @Darren_Murphy) and it is awesome. Use it with “Is Included In” to boot. 
(In my use case these relationships are not changed often so the seed/set-up time is when the ‘heavy’ lifting is usually encountered).
2 Likes
Using the array method worked – thank you!
1 Like