Using :group by rather than :each item for performance and WU

Tips
1

Suppose we have an arbitrary list of Aircraft, and each Aircraft has an Aircraft Type (both are data types).

Aircraft are associated with a company, but not Aircraft Types. So, let’s suppose we want to show the list of aircraft types a company has.

We could use Do a search for Aircraft (that belong to this company):each item's Aircraft Type, possibly with :unique elements at the end.

This is, I suppose, the ‘standard’ pattern you might use (to re-emphasise, this is an imperfect, arbitrary example).

However, I recommend considering using:group by. I think group by might be the most underappreciated Bubble operator.

Do a search for Aircraft:group by Aircraft Type:each grouping's Aircraft Type returns the same output, and is cheaper because it runs as an aggregate search.

With the each item approach, we have to return each item in both the Aircraft and Aircraft Type tables. With the grouping (aggregate) approach, we don’t need to return each Aircraft which reduces the number of things returned.

It also runs a little faster, if you compare both forms of the expression on the page and give it a refresh.

So, if you’re curious, give :group by some love if you’re ever looking for :each item’s Thing.

21 Likes
2

Thanks for this! Will try soon

1 Like
3

It makes total sense.
Because of my SQL background I particularly used :group by in most ocasions, but it’s good to know it’s also the best practice though.

4

This is gold.

Out of sheer curiosity, how exactly do calculate the speed of both expressions?

5

This is because workload unit optimization and performance optimization go hand in hand. When reducing the amount of work our apps do (priced as WUs) we improve the performance of the app.

In this example for fetching data, the performance improvement comes from the reduction in the amount of data returned in the form of character count, directly related to the way in which the group by operator as you’ve found out, reduces the number of items to return, so if you can return less data, you get better performance, with a side benefit of lower WU costs.

Thanks for sharing this. It really helps everybody if all apps are being built with performance optimization in mind.

3 Likes