No Code != No Thought

Why does it still benefit to have seasoned developers involved in a No Code project? People often think using because there’s no code you need no coders. But for complex projects, the architectural experience those people have can be a great benefit.

The No Code advantages of rapid development time and the ability to build your own solutions are still there, but keeping that experience available can be a wise thing to do.

Here’s an example of when that might be the case, to do with ensuring suitable performance.

When developing databases, as soon as you start storing large-ish amounts of data, you often need to look at optimising queries, i.e. making sure they run efficiently. It can be a very satisfying process, as often you’ll be able to make improvements in speed that are one or more orders of magnitude.

If you’re using our No Code platform Agilebase to build your PostgreSQL databases (and at the same time the apps which use them), you don’t by default see the ‘raw SQL’, rather tables and views are built visually, or with the help of AI. So you won’t be familiar with lots of the traditional optimisation techniques.

Agilebase takes care of some basics, like ensuring primary and foreign keys are indexed where appropriate for example, but for really complex views operating on lots of data, you may need to dig a bit deeper. We’re happy to help with this – just get in touch if you’d like to make sure your system is running as speedily as possible.

One recent feature added is the ability to send data to PgMustard. This is an exceedingly useful tool which can give easy to understand optimisation opportunities, given what can be quite complex and hard to read raw data. It recently helped us speed up a particular query from 50 seconds to 3 seconds run-time, making a vast difference to usability. See the Investigation section here.

However, this post is about a different way to improve performance, that of changing the logic of the view, the way it’s built, rather than trying to optimise the execution.

An example here is a query we improved recently for calculating the allergens present in a recipe.

To explain the scenario, every recipe has a list of ingredients. A recipe can itself also be an ingredient in other recipes, so the system is somewhat circular, or recursive.

For example, the ingredient Tomato may be in recipe Tomato Paste. Tomato Paste may itself be in a recipe Pasta Sauce and Pasta Sauce may be in recipe Spaghetti Bolognese. If you were to say a specific allergen was present in Tomato (not that that would be likely), it should bubble up and be marked as present in all of those recipes.

To achieve that, we built a workflow that considered all 14 possible allergens in a recipe and looked at whether each one was present or not in that recipe’s ingredients. If any of the ingredients contain the allergen, it becomes present in the recipe, if not it’s marked as absent. Only those which are different to what’s currently stored are actually updated. Then the workflow recurses, or runs itself again, to deal with the next level of recipes up, repeating until there are no more changes to make.

That sounds fine, but after a while we found the workflow slowing down as more ingredients were added. If you have say a thousand recipes and a thousand ingredients, with 14 allergens, that would mean 1000 x 1000 x 14 = 14 million comparisons have to be done.

What can we do to speed this up? Well it took a while to be honest, but eventually it dawned on us that you don’t have to check each recipe allergen against the cases of that allergen in all ingredients. 

Most of the time, allergens aren’t present in ingredients, it’s just now and again that you may get one or two in an ingredient. So if you first make a list of only the present ingredient allergens, then compare the recipe allergens against that, you can save a lot of work. Rather than 1000 ingredients x 14 allergens, it’s now something like 100 ingredients x 2 allergens on average.

If an allergen is in that list, then it must be present, if it’s missing it’s not present by default.

Technically, the SQL code that does the check is the bool_or function. It changes from

bool_or(ingredient allergen.present)

to

bool_or(ingredient allergen.id is not null)

Cutting the number of comparisons down by an order of magnitude speeds up the whole process by an order of magnitude too, from many tens of seconds to just a few seconds.

That’s one example of why using No Code to build a database app doesn’t mean No Thought! People with development experience and expertise are still really helpful to customers when problem solving.

That’s why we partner with a number of consultants, like Little House Consultancy and Empyrean Digital. They add great value to the Agilebase offering and have built lasting relationships with customers, helping them build systems robust enough to see their organisations through growth periods without having to worry about the technology.