One-to-many-to-many-to-many performance

Let’s say I have the following relationships for datatypes A, B, C and D.

A: List of B
B: List of C, Single A
C: List of D, Single B
D: Single B, Single C

If I request A will it also retrieve data in cascade from C and D? Or will it stop at B data?

1 Like

I answer myself.

It brings everything. Although right now I have a small subset of info in B, C and D.
I don’t know if Bubble has any mechanisms to control how much data it retrieves.

Lets say I have 1000000 D total records that pertain to A via C-B. If I load A thing will it retrieve those million records every time I load A(for the first time). Not talking about any cache mechanisms that Bubble may be using.

Or would it be wise to remove the “C: List of D” relationship? And only reference D to C via the Single C relationship.

Generally speaking, Bubble only loads as much data as it needs.

Some examples:
If you don’t reference the list at all on your page, it won’t load it.
If you do a List of D:count it will load all entries as it needs to count them.
If you do a List of D:first item it will load the first item.
If you do a List of D:sorted:first item it will load all items, as it needs to do the sorting before it can know which is the first item.

2 Likes

I appreciate your reply, but that is not exactly what I’m asking :slight_smile:
You are focusing on D when my problem is about A and its cascaded relationship with D.

Anyone?

Sorry to ping you @josh(as the lord of performance). It seems the community doesn’t know for sure or hasn’t seen the post.

Also interested in hearing more on this. Lists are great, but the more data my app keeps getting the more I think it’s best to avoid them if they result in slow loading data (especially if like you asked, if a List’s List fully downloads). I know in the past it has been discussed that Bubble tries to be smart with Lists, and there were certain areas I think they even mentioned they plan to improve - so would be interested in an update as well

1 Like
  1. Let’s be nice, please

  2. If A has a list of B, whenever you load A, you load all the ids of Bs in that list. We may change this eventually but no short-term plans to do this. We don’t actually load the rest of the data for the Bs unless you actually reference a property on the Bs. (I think! If you can build a simple test page that demonstrates otherwise, submit it as a bug)

  3. If you’re expecting to have a list > 100 items, I always recommend establishing the relationship in the other direction: have B have a single A, and then do a search for all B’s with that A. For lists < 100, it might be a little faster to do it via a list.

16 Likes

Thanks @josh .

I will as I have seen 3 levels of depth items.

Follow-up idea: Have you guys considered adding something like an advanced filter “:depth”

I.e. Current user:depth is “0” or search for thing:depth is “2” and it would retrieve items up to the level of depth you select.

It would allow keeping all the things nicely referenced directly and reverse while making sure you retrieve what you need at any given time.

Just a wild idea to help from end-user perspective creating consistent DB and for performance.

1 Like

Actually I had always thought that you did pre-fetch the list data.

And I think we tested it out at some point in the past, but now I can’t find it. But I remember it involved loading a page then turning off the internet connection !

We use a best practise of always connecting a relationship two ways: A for B and a list of B to A. But what you’re saying here is that unless the lists are <100 items this may not be good for performance? If A has also lists of C and D and E, everytime you load A it loads all the id’s of every of the other items attached as a list? I wonder how much that will affect the loading of A

2 Likes

Hi Vincent

I’m learning more about database design at the moment.

Would you please be able to elaborate as to why it’s best practice to link both ways vs just one?

This would helpful better understand how to structure my own database.

Many thanks!

1 Like

Hi, I am here as a Bubble learner and trying to deepen my understanding about performance. Reading the whole post I had this idea: could it be possible maybe, if a long list belongs to a data type, avoid to load it unless it is needed? What I mean is this. Let’s say A has a list of B. Maybe one has this setup because he/she thought the list would have never raisen up to become very long. At that point when a user do a search for A’s some other properties, if I am not wrong, Bubble load the list of B’s unique ids as well. But if the search didn’t need at all the list of B, could it be possible to (partially) load only the othe needed attribute(s) of A? Thank you!