From 20s down to 1s: How We Shaved off 19s from our GraphQL API queries

If you’re not born yesterday, chances are, you’ve heard of GraphQL.

Engineering teams at Netflix, GitHub as well as Shopify scaled their services globally by leveraging GraphQL (and many other tools of course), so can you.

GraphQL become popular among frontend engineers because one of its most striking selling points: Only querying the data you need.

Great developer experience comes at a cost, and oftentimes it’s the burden that backend engineers have to carry.

Not so long ago, one of our GraphQL resolvers was particularly slow. It had to retrieve hundreds of thousands of items in a single request, each item contains two, three, four if not more layers of nested data.

We’ve explored a few options to speed up our API: pagination, reducing the number of nested queries, caching, filtering, and a few more.

These approaches have been helpful indeed, but one thing caught our eyes: We prevented the clients from overfetching data, but we’re still over fetching from the database.

We optimised our SQL queries, and the results were phenomenal. Speaking from the many performance benchmarking tests we’ve done, the query that returns GBs of data went from taking +20 seconds down to taking less than 1 second.

Example walkthrough:

Imagine you are building a university HR system, and you have these 2 tables in your database:

Note: Database diagram made with https://dbdiagram.io/

Database design simplified:

In a university, each lecturer is an employee within the institution, they can deliver as many lectures as they want. We’re only storing IDs of the relational entities, which means whenever we want to look up the actual data of say for example, which employee is working as a lecturer for the Chemistry department, we have to find the Employee by the employeeId of that Lecturer record.

As for the API, we want to be able to make a GraphQL query that looks like the following:

query {
  findManylecturers(
    "where": { "department": { "equals": "Physics" } }
  ) {
    id
    title
    department
    employeeId
    employee {
      id
      firstName
      lastName
      email
    }
    lectures {
      id
      topic
    }
    managerId
    manager {
      id
      title
      department
      employeeId
      employee {
        id
        firstName
        lastName
        email
      }
      managerId
      manager {
        id
        title
      }
    }
  }
}

GraphQL query explained in plain English:
“We want to find out that for all the lecturers that work under the Physics department, what are their titles, what’re their names and emails, the topics of the lectures they’ve delivered, who are their managers, what are the titles of their managers as well as which department their manager works under, oh and also the titles of the managers of their managers, yup:)”

The query above involves quite a few moving parts: find the lecturers, find who’s working as these lectures, find what lectures have they delivered, find the managers of these lectures, as well as the managers of managers of these lecturers.

Our solution:

In our database layer, to achieve better performance, we can start only retrieving the fields requested by the client — — from the DB.

Executing SELECT * FROM Lecturer WHERE department = “Physics”is no big deal if you’re only doing it once on a single dataset with a few hundred rows, BUT it’ll come and bite you if you’re querying hundreds of thousands of rows, on multiple datasets, multiple times within a single API call.

This is what we can do instead:

SELECT id, title, department, employeeId FROM Lecturer WHERE department = “Physics”

You can easily adopt this query logic in the ORM tools of your choice.

By applying this approach for all of your SQL queries (or database calls in general), we can drastically reduce the payload size (and time taken) transferring data between our database and our servers.

😄 If you find this article helpful, please share it with your friends & your team.

💡 I’m always open to feedback. If you know any ways we can optimise our approach, please fire away in the comment section:)

🤝 How to connect with me:
GitHub: https://github.com/Mingyang-Li
LinkedIn: https://www.linkedin.com/in/mingyang-li-nz/