What is the most performant way to work with DB items created by Current User?

david43 · 2021-01-30T17:36:10.768Z

Having a bit of a developer background (though by no means an expert), I initially took for granted that I knew how to build performant Bubble apps. But I’ve now been questioning whether I’m doing some pretty basic things in the best way.

I searched this forum and on Google and did not come up with anything that addressed this directly, even though it seems basic so I may have been using poor keywords.

Suppose you have two data types - User and Things. A User can create a Thing.

Now imagine you want to display a list of Things created by the Current User.

Is the best way to do this to perform “Search for Things Created By = Current User”? Is this the most scalable method with potentially 10,000s of users and 100,000s or 1,000,000 of things?

My assumption going into Bubble is that concepts like DB indexes are automatically built for commonly searched fields like Created By, and I’m sure that’s true. In my old life, standing up a MySQL database with an index on the “Created by” field would give pretty impressive performance for a very long time, definitely into the millions of records. I know Bubble uses ES in various ways which should be far superior, even.

Let’s also say you want to display the total number of things created by the user within the last month. Should you do a text element that displays a “Search for Things created by Current User, where Created At is > -30 days:count” ? Or is there a better method?

What prompted me to start thinking about all this is that I have been doing things exactly as described above, and in the latter case (the # of things created by a user in the last 30 days), I’ve already seen a big performance slow down with only a couple hundred Things and 3 Users. The text element with the aforementioned count is the last thing to pop in on my page.

I’ve seen folks reference having a List of Things on their User object, but saw conflicting info on this as well and didn’t see much tactical implementation suggestions for how to best accomplish this. I’d like to avoid reconciling a list of Things on the user if it’s not going to be the most performant option in the long term.

Thanks in advance!

help · 2021-01-30T21:02:29.825Z

Hi,

Firstly don’t have a list of things on the user, because everytime the user record is loaded, all of the fields stored in the ‘thing’ list on that user is also loaded (which slows down at scale).

But apart from that, using Do A Search for operations with constraints like that is fine - the more filtering you can provide in the do a search for the more of a specific search (and therefore scaleable) Bubble can do.

You just want to minimise the amount of data returned, so having a specific Do A Search for that only returns exactly what you want is the best way of doing it.

Don’t use the Do A Search For:filtered operation for this because :filtered performs the filter after the whole list of data (potentially huge) has been returned. So put the actual constraints inside the Do A Search for call.

Hope that helps somewhat

Christophe_HK · 2021-01-30T23:41:32.961Z

Hey @help,

I agree that at page load, the more list of things the more time to load the data object. But when using a RG to show data from a user, the Current User’s list of things is more efficient than a do a search for things with user + current user, don’t you think?

Slow page loading time is a problem, but a slow RG is worst in my own opinion. Personnaly I prefer to wait for 1 or 2 more seconds for the page to load then a smooth user experience, than having a RG taking seconds to display data.

In fact, I think it really depends on the volume of data… In my apps, I mainly use list of things, to display the data but I also always add a field in the child data to reference the upper level. For me it’s a good practice when time is coming to perform some cleanup in the DB, because using list of things, if you delete the users, the things remain and you don’t have any way to find and remove easily the lost child data, still present.

Anyway, I will try the do a search to assess the performances. And I do recommand to use both the list of things and the reference field in the apps.

help · 2021-01-31T12:29:50.137Z

Yes as you say it really depends on the volume of data.

You dont want to be loading hundreds of user records and all their data on page load unnecessarily, but if its just the current user or one or two users then loading them and their list of things as a field on the user type won’t be an issue.

milindsonpal · 2022-02-15T14:41:32.886Z

You could bypass loading the list of data on page load by creating a separate data type that maps user ids to a list of things, and on the user data type you have a reference to the mapped thing

jason1 · 2022-02-15T15:04:18.504Z

You can utilize privacy settings to display lists of data quickly without the need to include a “Created By” constraint in many cases:

david43:

Suppose you have two data types - User and Things. A User can create a Thing.

Now imagine you want to display a list of Things created by the Current User.

Is the best way to do this to perform “Search for Things Created By = Current User”? Is this the most scalable method with potentially 10,000s of users and 100,000s or 1,000,000 of things?

If the Thing is something that only its creator should view, create a privacy setting that allows only that Thing’s creator to search, view, edit it in the data privacy settings. This setup can allow you to omit that “Created By = Current User” search constraint altogether and your data is safely protected from unintended access.