So, since the change in pricing model, and the introduction of WUs, I’ve spent a LOT of time (probably way more than it’s worth) testing many, many things in Bubble to establish the most WU efficient ways of doing standard (and not so standard) things, especially in relation to loading related data (lists versus no list etc.) and bulk data processing.
I’ve been tempted to publish my findings so far, for the benefit of others, but I’ve held back as I’m yet to be convinced that it’s actually a worthwhile use of anyone’s time trying to optimize Bubble apps for WU, when there are alternatives available that don’t have such heavy pricing/usage restrictions (but that’s a discussion for another time).
In any case, I thought I’d share this, as it’s a very common thing to need to do, and the difference in WU cost is significant – so if you’re doing this a lot in your app, how you do it will make a massive difference to your WU cost.
Also, this is as much a question to anyone who can explain why this is the case, especially any Bubble staff (I understand what’s happening but have yet to explain why). I’ve contacted Bubble support about this and they didn’t seem to have any clue what I was talking about – which doesn’t fill me with much confidence… if Bubble support don’t even understand WU or how they’re being charged, what chance do the rest of us have?..
Anyway…
The question here is ‘What is the most WU efficient way to check whether something already exists in the DB’?
For example, putting a condition on a workflow action to only create a new thing if there isn’t already one in the database (or one that matches some specific criteria).
Typically, people use one of the following expressions for this:
Search:first item is empty
or
Search: count is 0
So, is there any difference in WU between the two and, if so, which is the most efficient?
Well, the answer is most definitely yes, there can be a big difference – but it turns out neither of those are anywhere close to being the most WU efficient way to do this – so there is a better way.
Let’s look at the options in more detail…
For reference, I’ve done this testing with a database of 100k items, and each item has a list field containing 10k other items… so a pretty big database, and a very heavy datatype.
I’m using a simple workflow, with a single action to make changes to the current user, but only when the condition is met.
The condition will fail in all cases, so the action will not run – therefore WUs are only being incurred for the condition being evaluated. There are no constraints being used on the search.
The conditions are as follows:
Search: first item is empty – WU cost = 2.24
It’s easy to think that this might be the most WU efficient, as only one thing needs to be checked. That is true but here are the costs involved for doing this…
The search is performed (cost 0.3 wu) and a single item is returned (cost 0.015 plus the cost of the data – which here is very high).
Actually, my best calculation of the amount of data here only suggested a WU cost of 1.4 wu, so I’m not sure where the extra 1 wu came from, but in any case, the logs showed a WU cost of 2.24.
Search: count is 0 – WU cost: 12.28
In theory, this should be the most efficient way to do this.
The expression search:count is just an aggregate search (cost 0.2 wu) – it returns just a single number (and it’s very fast).
So simply checking whether that number is 0 shouldn’t incur any further WU cost.
However, for some unknown reason, that’s not what actually happens.
Whilst the expression search:count does indeed only return a single number and costs just 0.2 wu, for some reason adding is 0 to the expression causes it to return the first 10 items from the database (assuming there are 10), which means there is a cost of 0.3 for the search, 0.015 for each of the 10 returned items, plus the cost of the data – which for a heavy datatype like this adds up to a lot.
So, what’s the best way to do this?
Well, as I’ve said, the expression search:count is just an aggregate search and will only ever cost 0.2 WU regardless of the number of things it’s searching (I’ve tested this with databases from a few things up to 100k and it’s always the same).
So, simply storing the search:count as a variable somewhere, and then checking that variable is 0 will only ever cost you 0.2 Wu.
e.g. set a group’s content number to search:count, then check that group’s value is 0
Alternatively, and perhaps more securely (and seemingly inexplicably), simply turning the expression around, and having the search:count at the end, keeps the search as an aggregate search that doesn’t return any data.
e.g. 0 is search:count (rather than search:count is 0)
If putting this as a condition on a workflow, you can just use the expression arbitrary text (0): converted to number is search:count – and it will only cost 0.2 WU to evaluate.
So, in conclusion, here are the 3 ways to do this along with the cost in WU (on a large database of a heavy datatype):
Search: first item is empty – WU cost = 2.24
Search: count is 0 – WU cost: 12.28
0 is search: count – WU cost: 0.2
There’s a difference there of more than a factor of 60 – so if you’re doing this evaluation in many places across your app it can easily add up.
I’ve yet to receive any explanation as to why adding is 0 to the expression search:count causes it to return actual results rather than just the count (and in this case increases the WU cost by 61 times), so if anyone has an explanation I’d been keen to hear it.
But it’s simple to test this for yourself to see what’s going on – so if you’re using this type of expression regularly in your apps, I’d have a look and see how much WU it’s consuming.
Obviously, with smaller databases, and/or lighter datatypes the differences will be much smaller (I used a very heavy datatype here to highlight the issue) but it’s still worth considering.
, all of the above also helps you understand why