I have a live app that captures results for UK cycling events, it’s pretty popular with over 30,000 results captured so far. To help run some of the reports, I have a set of backend workflows that run overnight to populate certain tables in the database with summary data.
The problem I am having is that database operations (“insert into table” in particular) seem very expensive in WU. At the current WU usage levels, the price of bubble becomes unsustainable when the new pricing comes in later this year.
What is the recommended approach to this situation - do people use an external DB like Xano to run batch processes on data and then query the Xano tables from within bubble when the report is being viewed by the user?
If that is the approach, what are the recommended database platforms that people use in addition to Xano?
Learning xano could be an option but it will be another platform you are paying for or you could restructure your bubble data base to encorporate JSON data to greatly reduce the workload units.
If you want to chat more about this, i am happy to get in a slack channel with my developer who created a plugin called JSON assistance and see how we can help you
Are you sure your database structure is optimal? I think it’s rare that WU costs so much that it’s a significant portion (or more) than the site’s revenue (unless it’s an ad based business that relies on a large number of low value users)
Okay, that’s 30,000 things / 15,000 WU.
It’s this part that’s likely not as efficient as it could be. I’d be:
making sure it’s definitely more efficient to generate ‘summaries’ than it is to just query the data when it’s needed
ensuring I’m only doing the bare minimum needed (hopefully you’re not generating one report per user every day, because that’s not at all optimal)
What kind of ‘summaries’ are you creating? If you’re saving cycling events, I’d expect you have the following tables:
Race / Event (race details/date/time etc)
Result (position, time, Rider, age category, maybe a List of Splits)
Rider (rider name/age/any other info)
and possible Split (start distance, end distance, start time, end time, duration)
Would that be right? What tables are you populating with summary data? Is it a report unique to each cyclist? If so, you don’t need to do it every day - most people will only log in once a month. Instead, you need to just use normal DB queries only when the cyclist views the report / loads the page.
Could you elaborate on the general gist of how that works and how it reduces the WUs? I’d assume that rather than having a data type with multiple fields, it would be a single text field with JSON formatting for all relevant values associated with the datatype.
I have an example too. I have been trying to develop my own app and for 2 years I have had to put it on hold coz of one section that was blowing out and not working.
When I first started building it this one operation was taking over 1mins for bubble to do its work (I was still new at this stage) but over time I got it down to around 25 seconds.
Then bubble changed to the new system with workload units and for this operation to run (depending on the number someone selected) my workload units would be blown out to over a million
So I also looked into xano and spent month learning it and it is now another skill set I have coz I got really good at it but then I found the plugin JSON assistant and a very talented developer @NoCodeDataArtisan. He is also the creator of the plugin and he helped me change the data structure of this massive operation and now it works better than ever.
This operation would creates (if someone selects the maximum numbers) about 10,500 entries into the data base and they are lists, within lists, within lists, within lists.
Now…
It creates one database entry and my workload units for this workflow is under 100, it is crazy.
I know this is quite an extreme example but if @resultsheetapp has large lists or dataTypes with heaps of information in them, he could adopt a similar method and I have no doubt it would help greatly.
I am sure @NoCodeDataArtisan could shed more light on it as he knows way more than I.
As you’ve mentioned and shared about your use case, we utilize a single text field to store JSON, which contains a significant portion of the data. This approach is a popular database structure method, where developers use SQL (like a bubble database) and NoSQL (like JSONs) concurrently to leverage the benefits of both methods simultaneously.
We’ve recently introduced a new element called JSONata to the plugin, which is incredibly powerful and allows us to manipulate our JSONs with minimal restrictions. It even enables the joining of two JSONs.
However, its expressions can be challenging in complex scenarios, but we are here to assist you in implementing it and achieving your desired results.
Certainly, creating a hybrid database structure involves more than just incorporating a JSON field, yet it can assist users in reducing their Work Units (WUs) in numerous instances.
Moreover, we are keen to enhance the plugin to make it more robust and ensure improved integration with the Bubble database. Could you provide more details about your use case and the features you require to develop an enhanced hybrid database structure? This will enable us to incorporate these features into the plugin.
We appreciate you sharing your ideas with us; they are incredibly valuable.
[quote=“georgecollier, post:4, topic:322065, full:true”]
Are you sure your database structure is optimal? I think it’s rare that WU costs so much that it’s a significant portion (or more) than the site’s revenue (unless it’s an ad based business that relies on a large number of low value users)
It’s this part that’s likely not as efficient as it could be. I’d be:
making sure it’s definitely more efficient to generate ‘summaries’ than it is to just query the data when it’s needed
ensuring I’m only doing the bare minimum needed (hopefully you’re not generating one report per user every day, because that’s not at all optimal)
What kind of ‘summaries’ are you creating? If you’re saving cycling events, I’d expect you have the following tables:
Riders score points in the race depending on where they place, e.g. 100 points for a win, 99 for second etc. They score points for multiple scenarios though, so a rider could score 100 points in the Womens’ competition but only 50 points in the overall.
Events are grouped into “Event Series” (what you can see in the link above) and an event can be in more than one series
The Event Series calculates the points scored for the different scenarios. You can change the scenarios by playing with the filters at the bottom of the series page above, e.g. Road Bike vs TT Bike
The complexity comes from two places:
You need to create a deduplicated list of all riders across the entire series
The fact you can score points across multiple scenarios for each riders
I originally went down the path of using the bubble table on the event series page to run queries for the data, but if you have 100+ riders in an event series with 10+ events, then each time the report is run it is generating over 1,000 queries (100 x 10) which proved to be a problem.
The batch job I now have works in two stages:
It calculates the points scored for each rider for each scenario and stores that into database table 1
Then it rolls these up into a deduplicated list of riders into database table 2 along with their points results stored in a way that are easily retrievable
The bubble table then queries table 2 when the page is viewed.
The batch process is careful not to do any work it shouldn’t and only runs when things have changed.
My ideal is that all of this can be done when the page is loaded to minimise WU rather than a batch job but I just couldn’t make it work as I basically couldn’t find a way to be able do the “roll up process” in step (2) effectively on the client side.
@NoCodeDataArtisan Thanks for the information. I have put an explanation in the post above to George in terms of what I am looking to do - basically “Roll up” data into a points competition that can then be queried by the bubble table on the page. I am not a JSON expert, would JSON suit this kind to scenario?
Over the past two weeks I’ve been dreading having to spend the time on learning something new to just make a bit of savings on WUs for simple searches of mostly static data in my Bubble app. After reading the post and the comment of the JSON assistant plugin, I looked into it and I am SUPER excited to try it out and implement it into my one of my apps.
My initial thoughts of how to use it, are pretty much inline with the below
For me to make use of this, there will be some manual interventions necessary based on the approach I’m expecting to implement, but not of any significance that the likely benefits are outweighed by the minutes (time) it may require for my manual intervention.
Definitely looking forward to do some testing to compare WU potential savings on an app that will have large numbers of free users just browsing.
Hey dude! If you wanna chat I’m down. Using supabase or xano is a really nice alternative. But if that’s the route you go, decoupling front from backend, I highly suggest you go learn weweb. Bubble loses its advantage apart from the plugin library, imo, when you decouple from the backend.
Could you please share screenshots of your current database tables?
You can convert most of the data into JSON and store it in a single text field. Then, using JSONata, a feature of the JSON Assistant plugin, you can manipulate the data as needed. You can filter, modify, extract data, and even convert the results to a bubble data type (without consuming WU) for use in page elements.
This approach is faster and more cost-effective than integrating third-party platforms as a secondary database.
Tutorials are currently unavailable, but we are in the process of creating them.
To explore examples of how JSONata functions, you can view our demo and examine various scenarios of extracting or manipulating the main JSON using the plugin.
