So we’re starting to see some efficiency issues with our app, and I’m not honestly sure the best way to tackle this. Our main issue boils down to comparing 2 ways of fetching data, so I’ll summarize our setup below.
We have a dashboard that shows several stats widgets. Each of these stat widgets is referencing a list of things (Person A’s activities for a specific year). This list is a combination of things the Person A created AND things Person A’s account admin created. We use privacy rules and the “Do a search for” function to pull in the list.
My question related to efficiency is which below is more efficient:
- Use “Do a search for” just once, at page load or when the Person you’re looking at changes
- Set the state of an element to the list from Step 1
- Have the stat widgets on the page reference the element’s state from Step 2, using “element’s list :filtered” for each widget to do relevant calculations
- Use “Do search for” on EACH individual stat widget with specific constraints to what that widget needs to calculate
Other things to note
- Right now we’re using Option 1, but the “set state” action after doing the search is using up a large amount of our Bubble app’s capacity.
- There are ~25,000 things total across all users for the data type we’re searching, and that number increases by 5K-8K/mth
Thanks in advance for the help!
just to set some addtional background:
Do a search for - it’s a server-based operation
:filter or :sort- are client-based operations - so if you have an SaaS you have to consider your customers hardware as a possible limitation (despite data optimisations).
In general when I have a quite a lot of data to handle I always try to find a smart way of limitting the actual amount that I need to pull from the db. So for dashboard type of widgets I use dates as a main constrain for example:
Standard widget is to show all sales projects or leads across a timeframes. So then depeding on the amount of data I would use a default time peroid of a year or a quarter and then let the user decide if he/she want’s to show more or just change years / quarter or even custom date range.
But then you can use the UX magic and add a nice & clean loader to mask the time of database pull - the difference is that user will do this conciously so he/she might expect an additional loading time.
What I want to minimize is the first loading time.
You haven’t described the whole Search for in details but what I understood is that you’re mainly trying to get the same amount of data via different ways of searching / filtering but this is not the only way we can handle that.
Looking at your numbers - now you have 25k with couple of next months you will get to 50k and then 100k. Now or later - you will have to limit the amount itself or try to split the data load with some different tricks.
Ofc most stuff what I wrote is based more on my deduction of what you could do - I don’t know exactly what is your db setup right now and how you handle things - there could be some things to tinker there as well.
Hope this will point you into some new ideas at least
Agreed on @Guru 's point about limiting your search for data to a time constraint and taking into consideration that operators like :filter and :sort are done client side.
I have a CRM with thousands of rows of data and multiple users at the same time so i find ways to limit how much data is loaded on page load.
. Use time constraints
. Status constraints (eg. Loading “Open” and “Urgent” data)
. Divide different stats into different pages or different visible sections so data is loaded on user action after page load
If capacity is more of an issue vs users’ hardware, then it is better get more stuff client side.
In my CRM for example, my users do frequent searches in the customer database so i have a workflow that will build an index of customers names that is a ‘list of text’ state containing their “full names”|“unique id”. From which regex searches match names and then pull more data on that customer with a search for against their Unique ID
Other than that you should check your logs to pinpoint more areas that can reduce your capacity usage.
I didn’t realize that. That’s good to know!
For our app, I do currently limit the search with constraints based on time and the Person included (and privacy rules allow you to only see your activities and other “account” user’s activities)
So right now our search is like this:
Do a search for “activities”, with constraints:
- Date >= dropdown year’s value
- Date < dropdown year’s value (+1 year)
- activity’s users contains dropdown user’s value
This returns a list of that user’s activities for the full year the current user is looking at, then the app sets the state of a Group A to that list. From there, the widgets are looking at Group A’s list of activities and filtering from there.
My question is whether doing it this way or having all widgets do their own “do a search for” is best, but since searching is server-side, it sounds like that would increase our capacity usage vs. doing the search once, setting a state from that search, and filtering from there.
We haven’t received any complaints from users so far about the app being slow for their devices, so I think we’re good on that end.
@samnichols I highly recommend you check @petter’s book about the app performance - I haven’t found a better source of knowledge as of today - you will find quite a lof of interesting and important stuff there - maybe when bubble will release the infrastructure update, then some things could potentially change but that’s aimed for end of month if I remember correctly.
Now back to your question
Can you show me a print screen of your table ? How many columns you have and what are the particular data types there.
Still for widgets I think I would try with separate Do a search for’s - because that will allow your app to get the data on the page load but splitted into chunks and loaded when ready - so I think it’s better to download smaller chunks over the time then try to download a huge batch into user browser and then try to mainpulate them. If you want you can even program a dedicated workflow just for data loading and use for example RG Display List command one after another via Custom events.
Then If your data table of Activities includes like 20 or 30 columns inside - I would first optimise it via satelite data concept that 's also explained in Peter’s book.
Long story short:
You have two tables: Data table and Index table (at least I like to call it that way)
Data table - can be as big as you want because you will never use it for RG’s - it’s just a data container.
Index table - it’s a performance friendly version of Data table - so it includes only data that are necessary for searches etc also you can use one text field for example to include data from different fields from Data table.
Then the Index table and the Data table are related to each other via ID so you can access the data from one to another.
In your scenario for widgets you use Index table but when user want to check details of activity you fetch that one particular id from data table and display everything.
You also have to consider data lifetime so for example you can go even further and plan Archive Table so your data concept would on the end look like this:
- Activity Search Index (for searching)
- Activity Data (for displaying details)
- Activity Data Archive (all old activities that users can access via different view and different UX)
All those tricks will help with your capacity and all of them can lead to better performance but then again if your app is successful and your user base will grow on the regular basis, despite the best possible optimisation tricks you could imagine, there might be simply a need to increase the unit amount of your bubble plan because your business is growing
Total app performance is huge mixture of what you have under the hood but on the end it’s all about end user experience - if your users say it’s all good - it’s all good
Thanks for the thorough response! I’ll definitely check out that book. I didn’t expect my app to grow quite like it has, but yeah, I might just need to upgrade our Bubble plan to have more capacity. We’ve got over 500 users, still on the Personal plan, and I was hoping to get to 1200-1500 users before we had to upgrade. Just trying to stay as lean as possible, lol, but our app has become a lot more complex in the last month, so I might have to bite the bullet and upgrade.
People don’t share their user to bubble plan ratio quite often here on the forum, but I think personal plan costs vs revenue from 500 users that feels like a damn healthy business
Congrats & keep up the good work mate!