Forum Academy Marketplace Showcase Pricing Features

Any ideas on speeding up read/write to database?

Hi all,

We’ve just launched a section of our bubble app into our organization!

Been pretty smooth so far, the only issue we have is the speed of reading/writing!
Basic outline of the section - A ‘daily’ list of scheduled jobs that can be added to/edited. Shown in the image below.

Now the image only has a few scheduled jobs listed - in reality there are around 60 or so jobs in the list for each day.

In the editor preview app everything works pretty well in terms of loading and writing data, but in the live app it is incredibly slow! Adding a job in the live app takes over 5 seconds to run the workflow (almost instant in the preview app). It also takes a long time to load all the scheduled jobs for the day in the live app, but its relatively fast in the preview app!

It’s really causing trouble for us here, all the nay-sayers are trying to push us back into using excel spreadsheets!

Hopefully someone has some ideas on how I can speed the live app up! Can happily provide any extra info if needed :slight_smile:

1 Like

Hi Luke

Could you maybe describe a little bit, how you setup the database?
Or maybe duplicate your app and give us a link or something so we can look into it. I have an idea why is not working, but i need to take a look first

Regards Claudiu

Would be interested to hear ideas on this as well

Thanks for your replies!

Here is the duplicate I just created - though I didn’t copy the data across as we have a fair bit of private information in the system! Hopefully this is enough for you to see how it is all structured :smile:

[removed broken link]

The page is; allocations - I’m halfway through adding a fair amount of stuff so its a little bit all over the place at the moment.

I’m thinking this slowness may be caused by the amount of repeating groups all with different sort functions (in the live app there are 8 repeating groups on the page, with the new additions there will be 24 repeating groups!!! All with slightly different sortings).

EDIT: Also there are a fair few extra workflows/additions that don’t necessarily need to be on the page. Things such as clicking on the Truck/Trailer text to bring up a popup detailing everything around the asset could be removed from the page if its causing trouble. There are so many moving parts on this page it’s hard to work out whats causing the issue!

EDIT 2: Just added 4 new repeating groups to the live app (from 8 to 12) and can confirm after 2 or so hours of use the speed has not changed (hasn’t sped up or slowed down based on a range of timed actions), which I guess rules out the large amount of repeating groups as the issue of slowness!

EDIT 3: Oddly enough its still very speedy in the preview app version! Not sure why the live version would be any different?

Firefox warned me about long running scripts when loading the editor with your page!

Looking at the data structure, you may be able to apply these in places:

  • Primary fields other than unique id.
  • Define data views for often used constraint value constants, (Edit) and where less than the full set of columns are needed. Unfortunately views are currently just for use in the app data area, so this is not something useful to speed up data access.
  • Reduce the need for “search on table constrained by search on other table” by denormalising.
  • Privacy roles can help reduce data if done right.
  • Move some constraints from :filtered into the search, for example deletestatus = “no”. Filtering takes place after the main query, whereas constraints in the query reduces the records to be processed.

I suspect that some of the slowness is from having so many elements on the page that are responsive to data changes.

Repeating groups might give some speed advantage over the multiple grid of separate buttons etc, but that would be a major design difference, and speed improvements aren’t guaranteed, haha!

What sort of timings are you getting?

1 Like

Hi @mishav,

Could you please elaborate on this?

Thanks very much!

P

In the App Data area of the editor, there is a “New View” button, to define a view which can have less fields and less rows than the data type being viewed, for example just Falcon model cars:

I mistakenly assumed that views would be available as a data source in the workflow and design editor, but they aren’t there … @emmanuel would this be a change to Bubble worth considering?

Wow thank you for your help, really appreciate the depth you’ve gone to!

I’ll definitely look at moving :filtered actions into the initial search, though when I was first setting this up I remember it caused a fair few issues with the wrong data showing (though I’ll confirm when I go in and do it tomorrow).

Quick question on the following;

How would I go about this? Not 100% sure if I have any tables that are constrained by other tables - maybe the ‘Assets Available’ one?

Consider it as a general tip to look for (meaning: I didn’t look deep enough to find any either!) It usually only helps where table joins end up with a huge amount of data.

Your app might benefit from Bubble’s database administrators running a query profiler to help find the pain points. I’m not sure whether that is an option, no harm in asking though :slight_smile:

Another diagnostic might be to sniff the network traffic from the app, and compare it to a simpler app with similar data. It’d be a challenge to find someone with the skills to make this quick and useful.

I’m impressed to the level you’ve taken the Bubble app!

Good luck!

No worries at all,

Thanks I’ve really put so many hours into this! I’ll follow up on some of those tips, once the speed issue is solved a huge weight will be lifted! :smiley:

I agree this sounds odd! Some possible explanations, based on guesses …

  • Live version’s app or database servers might be under more load than development (more end-users to cope with).
  • Live version may be using different cdn servers, or different minify scripts, which may be less optimal than the dev ones.

If you can load both dev and live with the same data set, DBA or sniffing tests may be more fruitful.

I hope these breadcrumbs lead somewhere useful, or at least to fat birds.

Also, another question for my future planning in regards to speed of both the database and app itself - is it worth creating multiple apps on bubble for each department? For example, the above one is Operations - there are also Maintenance, Human Resources and Compliance departments that will all have their own system eventually.

Initially, I was going to do it all under the same app. But I’m a little worried this may put a lot of stress on the app with the sheer amount of data and backend users there will be compared to how its going now. In saying that, Operations is by far the most complex in terms of data - especially considering how many processes are required on a single ‘allocations’ page.

Have a partially set up Maintenance section (only for Purchase Orders, as seen in the test editor too), which seems to run pretty well! Takes a while to populate the repeating group (only one on this sheet! limited to 20 rows), around 10 or so seconds (similar to loading a day of allocations on the operations side) but once its loaded it works pretty well.

EDIT: I’m not using our work network right now, which may be an issue in terms of testing! Internet at work is literally over 100 times faster then the terrible terrible ADSL I have at home :smile:

I’d much rather have everything in the one app because everything ties in with each other - basically the whole reason I’ve been pushing our organisation away from all of these separate apps/spreadsheets into something like bubble!!

Hopefully some other people here who have live apps can comment on the performance merits of different pages in one app vs sub apps vs separate apps connecting with single sign-on.

I think that performance impacts from a large number of objects and data fields would be higher than that from the number of data rows or the number of end users, which are both easily scalable.

I’m having a second look …

Where there is “[filter constraint] and [another constraint]”, separating them into individual constraints is cleaner and it’s easier to move them into a search constraint if appropriate.

Converting dates to strings for comparison is more work for the database during a search than comparing dates. Using “change minutes to 0” and “change hours to 0” may be a faster alternative, for example …

RepeatingGroup allocation VIC 2leg has a search that is nearly the same as the search in the row above it, with some added filters. The search could be done once, stored in an invisible repeatingGroup, or in a custom state, and then the results of that search could be filtered differently for each row.

Also the same search can be reused for NSW and the other states.

Could use nested repeating groups to avoid having nearly duplicate sets of controls across states, and across different legs. Then the workflow would be easier to maintain too.

I’ll definitely work on these two tips. My main issue with the dates was over here in Australia we use day/month/year, so majority of the dates in Bubble had to be re-formatted! But in terms of searching there is no need to have them reformatted at all, its more just a mindless thing I did I guess!

Just some background on the repeating groups, as you can see there are 6 for each state. Initially, as SA is set out, there was only 2 (1 for current day allocations, and 1 for intransit allocations from a previous day). Now I’m implementing a changeover system that allows any job to have up to 3 separate legs (3 different stop overs for the same allocation, which has also resulted in 3 intransit repeating groups to cater for 3 different ‘loads’ being 1st leg, 2nd leg, 3rd leg). - Sorry if that makes no sense at all.

Basically, are you saying I should maybe go back to 1 repeating group for current day (and have the 2nd and third legs hidden in that repeating group, which could somehow appear when needed) and then 1 for intransit loads in a similar fashion?

When you say Nested repeating groups, what exactly do you mean? (Sorry still relatively new!)

Hi!
Just from looking really quick at your app, here’s a suggestion:

You have a lot of repeating groups doing searches for “allocations”.

  • Instead of every RG doing searches for this, create one RG that does this search, called “Allocation Source”. This RG could be just 1x1 pixel, so that it’s invisible.

  • Make the data source of your other RG’s “Allocation Source: Filtered”
    This way, you only make the search once.

  • Make as many of your search constraints possible in the “Allocation Source” RG. This would be the ones that are the same for every RG.

I think this would speed up your app quite a lot. :slight_smile:

3 Likes

Thanks @pnodseth!

I never even considered that sort of structure, but it actually sounds incredibly simple and straightforward!! It sounds a lot like what @mishav was saying now I understand :smile:

This would reduce the repeating groups dramatically! I’ll only have 2 doing searches (one for current day, one for intransit), and all of the other repeating groups will then gather its data from those two repeating groups.

I’ll give it a proper go on Wednesday when I’m back at work

Thank you guys so much!!

Great, no problem!

I’ve had a bit of a go tonight in a test page using the live app data, unfortunately results aren’t great but it has highlighted some interesting notes;

  • Using the main allocation repeating group to first search for the data and then the other repeating groups :filtering the data from the main allocation repeating group
  • took 17 seconds to load the data with 8 repeating groups (1 current day and 1 intransit group for each state)
  • also took 17 seconds to load the data with 24 repeating groups (3 current day, 3 intransit for each state)

Which indicates to me that the number of repeating groups doesn’t seem to be an issue?

  • Also, made a big improvement (from 17 seconds to 10 seconds load) by removing all of the conditional formatting I had set up for detecting duplicates, which turns out every one had a ‘search for’ and then ‘filter’ function, summing up to around 144 different conditional actions.

I’m going to sift through the rest of my past work, and just pray that I find some more crazy amounts of ‘search for’ or ‘function’ actions.

This means an outer Repeating Group, and inside its cells is another Repeating Group that uses it’s parent’s data.
It could mean having to draw the line of buttons and elements just once, and repeat them for the different combinations of state, legs, etc.

It sounds like you’re on a roll now :slight_smile: