How we improve platform performance by optimising our SQL queries

SQL is critical to our customer-facing products, like 360, University and Studio. Making sure those products work quickly is a cornerstone of the UX. As our business grows, the need for optimised SQL queries grows too. In this article, I’ll explain how Builder.ai optimises SQL queries and how optimisation is baked-in at a team level.

What is SQL?

SQL (Structured Query Language, often pronounced “sequel”) is a programming language used to manage and manipulate structured databases. It allows users to store, retrieve, update and delete data.

SQL queries are commands written in SQL to interact with a database. Using commands like SELECT, INSERT, UPDATE and DELETE, SQL queries can retrieve, add, modify and remove data and records. SQL is essential for working with database systems like MySQL, PostgreSQL, SQL Server and SQLite.

At Builder.ai, we use SQL across all our internal and customer-facing products, like 360, University and Studio. And as we grow, it becomes ever more important that our SQL queries are optimised. Otherwise, performance will be impacted and user experience will deteriorate.

The need for SQL query optimisation

Poorly written SQL queries take a long time to run, especially when the dataset is large. This slows down page load times, increases server load and prevents system scalability.

These three are all vital for our business: customers need a smooth user experience, our servers need to work optimally and we need our technology to be able to scale with us.

It is well established that slow pageload times have a big impact on UX and bounce rates. Research by Google found that 53% of website visits are abandoned if a mobile site takes longer than three seconds to load.

As a customer, I don’t care about what’s happening in the background. I just want to complete my intended journeys as fuss-free as possible.

SQL best practices

Now, I’d like to shed some light on the SQL best practices that our internal teams follow, and how we ensure all our teams write optimal SQL queries.

Data normalisation

One important thing to get right, if you're creating databases and tables, is normalising the data. Data normalisation is the process of organising a database into structured tables to reduce redundancy and improve data integrity. Each table should focus on a specific subject, which works better than storing all information in a single, large table.

This approach reduces data redundancy and improves query performance, especially as the dataset grows. Normalisation helps by splitting complex data into related tables, improving both efficiency and scalability. There is a trade-off to consider here, in that normalisation increases usage of JOINs, which can be computationally expensive in large data sets. But for our purposes, normalisation is usually better.

Indexing

Another best practice that we use is indexing. Indexing in SQL is a technique used to improve the speed and efficiency of data retrieval operations on a database table. An index works like a lookup table that allows the database to find rows faster, similar to how an index in a book helps you quickly locate information.

Indexes are created on one or more columns in a table, allowing the database engine to search and sort data more efficiently. If you're asking about any specific item, indexing allows the code to directly jump to the relevant records rather than traversing whole tables for it.

Query caching

A query can be pre-compiled or cached to improve performance by reducing the time taken to execute repeated queries.

Query caching involves storing the result of a query after its first execution. When the same query is requested again, the database can reuse the stored result instead of re-executing the query. This significantly speeds up frequently run, read-heavy queries.

There is a balance to be struck in cache lifespans. If the cache lifespan is too short, queries may still need to be re-executed frequently, reducing the performance benefit. If it's too long, stale data may be served, leading to inconsistencies when the underlying data changes. We try to balance performance gains with data freshness.

Don’t optimise too early

Premature optimisation is a well-known pitfall that can make code unnecessarily complex and introduce bugs. It’s important to make sure code is accurate and does what it should do without errors. A further consideration that sometimes takes precedence over optimisation is readability. Your SQL queries need to be understandable by your future self or other members of your team.

With accuracy and readability established, you can optimise your queries.

Enforcing best practices across a team

It’s no good having one developer writing well-optimised SQL if their peers are producing very slow queries. For the sake of accuracy and readability, a team needs to approach optimisations in the same manner.

I have been involved in teams that use Rails to create queries. Rails is a developer-friendly framework that allows you to write queries easily – but it has limitations because you have to abide by a set of rules. And these can be inefficient. So I discussed with the team about using raw SQL queries, which is faster than the Rails version.

I often say to developers, if you want to achieve something you have to deviate from the easy to the complex. You have to work outside the box. You have to write your own logic beyond the framework. It might be built with the help of the framework, but you have to write it.

One of my developers was having trouble getting response times down for a dashboard. I encouraged them to go beyond the Rails framework to create raw SQL queries. This approach helped the developer reduce the response time by 90%.

But there’s not a one-size-fits-all solution to SQL query best practices. How we approach something will always be based on the product requirement. Whenever we have a new feature to work on, we will decide how to approach it. If I just want to show a simple dashboard or a simple detail page, for example, it can be done with Rails. This comes back to “don’t optimise too early” - learn when to use the more complex techniques, and when to keep it simple.

Closing thoughts

The complex backend requirements of our products, and the products we build for clients, make SQL best practices a cornerstone of our success. Click here to learn more about the products mentioned in this article and how Builder.ai’s AI-powered development processes can bring your ideas to life.