Major database performance improvements for agileBase

I always enjoy the chance to do some performance optimisation work on agileBase – especially when you figure out how to improve the performance and responsiveness of the system in a way that customers will definitely notice.

Now is a good time for us to be looking at this. Over the past year, more customers have started doing more complex things with greater amounts of data. Here’s how our database workload has increased over that time:

So here are a couple of updates that will be released next week:

1) Auto-updating cached views used for calculations

View caching is a feature first introduced in 2015. It can improve the responsiveness substantially – views which previously would have taken many seconds to search now return data in milliseconds.

A ‘primary’ view, that is one which a user looks at directly such as a sales report for example, is often a good candidate for caching. It can be set to refresh the cache whenever data changes so the user always sees up to date information.

However until now,  ‘underpinning’ views which a user doesn’t directly query, but which are used by those primary views (e.g. in joins) haven’t benefited as much from caching. Until this update, they wouldn’t automatically refresh when data changes, only updating once a day, meaning data that users see could be out of date – that’s not always an issue but sometimes can be.

An example from an actual customer is a complex and relatively slow VAT calculation (when run over many records), defined in a view ‘vat calculation’, which is then used in dozens of other views.

With this new feature, that underpinning VAT calculation view can be cached and set to update whenever data it depends on changes. Those multiple other views that reference VAT now have massively improved performance at a stroke!

This also works wherever a view is used in a chain. So if View 1 references View 2 which itself references View 3, View 3 can be cached and will update on demand whenever a user looks at View 1.

This one change should have a positive impact for many customers.

Once again, we couldn’t do this on our own, this was a real team effort. We have to thank two customers, Beacon Foods and Britannia Windows along with our partner Richard Wilson of Little House Consultancy for the time they spent in conversation with us as we progressed this idea.

2) Query Optimisation

Our second update is more niche. The feature will be enabled automatically, so administrators don’t need to configure anything. Technically it’s to do with re-writing the WHERE clause in the SQL that agileBase generates to take advantage of primary keys in certain situations – please contact us if you’d like to know the full details.

This change will only affect a few views, but one of those which will be improved significantly is currently taking about 20% of total query processing time over the whole system!

Our testing shows that each call to this view (and there are tens of thousands per day), which currently takes about 0.7 seconds, will reduce to a few microseconds, hence freeing a lot of server capacity for all users to benefit from.

Thanks must go to Oliver Humpage of Entuplet for being a part of the conversation which initiated this.

We always want to keep the system running like a well oiled machine for whatever scale of use customers need, so our work doesn’t stop there. Next on the list is looking at, amongst other things, scaling across multiple servers. We will keep you posted.