Search: count is 0? Or Search:first item is empty?

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 emptyWU cost = 2.24

Search: count is 0WU cost: 12.28

0 is search: countWU 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.

79 Likes

Thanks for sharing that. My guess on the greater than expression, as you say the count is 0 brings up the 10 items and associated data for some reason, maybe the greater than expression causes it to cycle through each returned item in attempt to find the actual count number before evaluating if it is greater than.

8 Likes

Woah! This is quite big.

My app (and surely many other apps) is filled with this kind of checks.

This WU business is so convoluted. How do we even make Bubble understand madness of it? :frowning:

We definitely need many of such tips, and also to have Bubble fix these issues, and better still to get out of this madness and come to some simpler way of checking processing workload and much better pricing for those.

3 Likes

Hmmm… but the actual count (in this case 100,000) is returned straight away, by the aggregate search - so that number is already known…

1 Like

Your tests are a great benefit to all of us working hard to push through this WU nonsense @adamhholmes! I hope you share more of your findings.

I am thinking that there must be a corelation between >0 returning items and a :count operator returning items if the number of items is small (which if I remember correctly was something you discovered and verified in a different post). In any case this does sound like something Bubble should be working on.

I don’t have a large enough development database to test it myself but would arbitrary text(search:count):converted into a number > 0 be just as cheap as 0 is search:count?

5 Likes

This test and tips highlight why the new pricing table was not ready to go live and why Bubble have a lot of job to do on their side to improve WU consumption.
A user should not wonder if =, > , putting thing before another or use arbitrary text will cost more or less.

Actually, the monthly update just sow doubt about priority in Bubble dev and this is somthing that @josh and @emmanuel need to address and switch their priority.

The first priority that come in mind for me is improve search function to add more possible constraint that actually need to use advanced filtering and have OR function to avoid using merge.

8 Likes

Actually, you don’t need a large database to test this - as it makes no difference whether you have 10 things or 10k things - it’s the same WU cost regardless.

It’s the size of the returned data that makes the difference.

To answer your question, yes you just need to produce the number 0 (which is a surprisingly difficult thing to do in Bubble - you can’t just type it at the beginning of an expression)… so converting an arbitrary text to a number, or doing a count of an empty list on the page - will all give you the number 0 which you can use in the expression (although relying on something on the page is not as secure as doing it directly the the workflow action - which may or may not be an issue, depending on what you’re doing).

4 Likes

Epic post @adamhholmes ! Huge thanks for sharing!

We all Bubblers need to remember who got us into writing expressions in Bubble like Yoda would. :wink::+1:t2:

4 Likes

Second this. Before WUnderland announcement and rollout there was no difference (at least “known” difference) between any of these operators in terms of :count. Now devs have to check every single aggregation (count)…

3 Likes

This is hilarious.

@adamhholmes thank you for this great analysis. You’ve just saved a lot of WU consumption for a lot of apps :slight_smile: :clap:

6 Likes

A lot of fun like
If you do
Do a search for:count:rounded to 0 > 0… This is enough to keep maggregate instead of msearch

Also, if you do Do a search:count > State … maggregate

or … (Do a search for count *1) > 0 … maggregate…
So now… Should we consider Do a search for :count > 0 … a bug?

7 Likes

Or a feature :grinning:

3 Likes

Wow, that’s incredible…

On further investigation I can also confirm this… generating the number 0 any way other than typing it keeps it as an aggregate search and a WU cost of 0.2

There’s something about typing the number 0 that makes Bubble decide to make a search and multiply the WU cost by 61 times (in my example case).

Even typing ANY other number is fine (i.e. search count is 1 keeps it as an aggregate search and cost only 0.2 wu).

It’s just the number 0 (when typed) that causes this.

So, you could just use the expression search:count < 1 and it will only cost for the aggregate search.

So (in my specific example database):

search count is 0 = 12.28 WU
search count < 1 = 0.2 WU

Surely that’s not intentional behavior (or is it?!) @nick.carroll any insights here?

8 Likes

This feels relevant here:

One tiny “0” instead of “1” could cost someone an insane amount as it stands now (sounds like accounting?)

8 Likes

In your tests can you please also check if putting >=1 produces same amount of WU?

Even if it is a bug and Bubble comes out saying that this is a bug, they are fixing it etc., how many such bugs might be there for which we are being forced to pay price for apart from so many inefficiencies in search, list handling etc.? And will they refund money to people who became victim of this bug and ended up paying a lot?

There’s a big flaw in philosophy of calculating WUs as compared to checking actual processor consumption. It requires calculations to be super super accurate and updated. And Bubble doesn’t seem to appreciate is that calculating WU for every every action itself is such a processor consuming task. And we only end up paying cost for those calculations too!

6 Likes

I’ve been waiting for this all day :slight_smile:
Screenshot 2023-06-02 at 23.06.46

3 Likes

Super interesting and I had not thought to test this.

This may not exactly be a bug, but here’s the explanation as far as I can tell:

Basically, search:count is a special database operation that, as @adamhholmes notes, simply returns a scalar number. That is to say, this expression is of number type and does not return an object (which is what a search generally returns). When expressed this way, the database seems to return a number directly to the page.

(And as @adamhholmes also notes, if the search is unconstrained, this result is returned practically instantaneously, even for very large databases. I have also observed this in the past and talked about it in other places.)

Typically, a search returns a Bubble List object. (We know this from the plugin API.) A List object is a JavaScript object with several methods on it that allow us (in the plugin API) to do two things:

  1. Get the number of items in the List via the .length() method. And
  2. Get the elements in the List (whatever they may be) via the .get() method.

So, in the plugin API, we can in fact get the length of the List without having to inspect any of the items in the List, if that’s something we desire to do. (List Shifter actually takes advantage of this and publishes an “item count” exposed state which becomes visible far in advance of any of its other states).

The :count property of Lists as seen in the Bubble expression builder is exactly this method in action. The count is the .length() of the array of items that we would have if we were to .get() the entire array of items. (And, in fact, we need the length value because .get() allows us to access any of the items via their indices as a range, so, for example, to get the entire list, we say in JavaScript: List.get(0, List.length()-1)

This resolves to a JavaScript array whose elements are of whatever type the List is. Note that, if the List is a List of Things, the individual elements will themselves be objects (a Bubble Thing object which, like a List, is an object with several methods on it that allow us to understand what properties (fields) the Thing has and to get the values of those properties).

Now, why do I give you this crash course in the Bubble plugin API?

Well, a search always returns a List except, it seems, when we do the special search operation search :count (which returns a scalar number, not a Bubble List object). This isn’t documented anywhere AFAIK, but it’s demonstrably true (as Adam has shown).

So, basically, when we do a search, we are saying, “hey, I want to fetch the items that meet such and such criteria from the database”. And then you might further operate on that result using other list operators.

Unless we say (in a very specific way) search :count. (Again that is interpreted as “just get me the number”.)

And now we might ask, why is it that an expression like:

search :count < 1

is different from:

search:count is 0 (where 0 is typed into the Expression Builder)

Well, I expect that it’s down to some of the vagaries of type coercion in JavaScript and how the various Bubble operators are implemented. It might also be related to how the Expression Builder understands and assigns a type to the expression being built.

I haven’t tried this myself, but what someone might want to do is turn parens on and build:

(search :count) is 0 (that may or may not be possible, I’m not sure.)

In Bubble, the token “is” is the equivalency operator. In JavaScript, the equivalency operator is ==. There is also a strict equivalency operator, ===. The == operator will attempt to coerce the operands to the same type before evaluation. The === operator will not. So:

false == 0 is true (because in JavaScript, both false and 0 are falsey)
false === 0 is false (because while both operands are falsey, they are not of the same type and so are not strictly equal)

Bubble’s equivalency operator, is, is available to any type of expression (similarly to how == and === are always available to us and can have operands of any type in JavaScript). This will be important in a moment.

In Bubble, the token “<” is a numeric operator. It is only available as an operator on a number or an expression that is seen of being numeric in type. (And this is true of the other comparison operators as well.)

In JavaScript, the less than operator is < and, unlike the equivalency operator (which has both loose and strict forms), there is no “strict less than” operator in JavaScript and, if the two operands are of different types, various type coercions are undertaken in order to perform the evaluation. So for example:

"4" < 5 is true (the string "4" is coerced to the number 4 and compared to 5 and 4 is in fact less than 5 so this evaluates to true) and also
4 < 5 is true

What seems to be going on in Bubble is something like this:

search :count < 1

Here, using a strictly numeric operator forces this expression to be numeric. search :count is interpreted as the request for the database to return the number of items, not a List. I guess that using a numeric-only operator implies to Bubble that we have no need for the List itself.

But:

search :count is 0 (where zero is typed into the expression builder)

is seen as “indeterminant” by the Expression Builder. Recall that is can be applied to any type of expression. So, in this case, where the expression could be interpreted in one of two ways:

  1. “get the results of this search, get its length, evaluate if the length is 0”
  2. “get the number of items that satisfies the search (the special search search :count), evaluate if that number is 0”

Is interpreted as in option 1, not option 2. Personally, I suspect this is related to both the nature of is and how typed characters are interpreted in the Expression Builder. You could argue this both ways, I suppose, so on one hand it might be viewed as a bug (it’s not consistent with the behavior in the case of the numeric comparison operators), but on the other hand is is not strictly a numeric comparison, it’s an “anything” comparison.

But then, of course, Adam points out that using other numbers seems to work just fine. So this is probably some sort of bug that relates to the fact that 0 is falsey (these sorts of errors are quite common and easy to make in JavaScript) and the behavior is not intended to be as we observe here.

I leave it to someone else to file a bug report. :wink:

17 Likes

Not possible. Was thinking the same.

3 Likes

While I didn’t elaborate on this in my overly-long reply :point_up:, all of the above also helps you understand why search :first item is heavier than search :count, in case that wasn’t already obvious. (search :first item will return a List object with one element and that element might be null (is empty in Bubble nomenclature), while search :count just returns a number.)

5 Likes

Yeah, this was one of my first thoughts too… and one of the first things I tried…

2 Likes