How do I reduced the API response time by 10 times

Problem

I received this error after a prolonged period of the request being pending

bash
"stack": "Error: Failed to convert rust `String` into napi `string`
 at RequestHandler.request (/app/node_modules/@prisma/client/runtime/index.js:49022:15)
 at async /app/build/services/data.service.js:8:20
 at async PrismaClient._request (/app/node_modules/@prisma/client/runtime/index.js:49919:18)
 at async getData (/app/build/services/data.service.js:16:25)
 at async basicStatisticsController (/app/build/controllers/statistics/index.js:16:48)"

This error only occurred in my production environment, not in the staging or development environment. This suggests that it might be related to the difference between the data.

Explain & Solution

It’s a nodejs project with prisma as ORM and database management is MySQL

I have 3 tables related with each others looks like the below diagram

Let's say I need to count the total books and published books belong to a specific category (let’s say “science”) and are made by publishers located in Hanoi.

Initial approach

The current solution is I query all the publisher located in Hanoi first, it looks like this in prisma.

jsx
const publisher_ids = prisma.publishers.findMany({
	where: { location: "Hanoi" },
	select: { id: true }
})

And then query all the books belong to above publisher_ids and use nested reads to take the data about book’s category.

jsx
const books = prisma.books.findMany({
	where: { publisher_id: { in: publisher_ids } },
	include: {
		category: true
	}
})

Finally, I loop through those books to count the published and total books.

jsx
let publishedBookNumber = 0;
let totalBookNumber = 0;

books.forEach(book => {
    const filteredBooks = books.filter(book => book.category.title === "science");
    publishedBookNumber += filteredBooks.filter(book => book.is_published).length;
    totalBookNumber += filteredBooks.length;
});

This is problematic 🙈 it could potentially cause the application to fail when querying a large volume of data. In my case, I have over 20k publishers with millions of books, which is why I encountered the error.

More information, check this github issue.

New approach

The requirement here is simply to count how many published books and the total number of books are there. So it’s perfect fit to use group by and count together to archive that.

jsx
const booksGroupByPublishOrNot = prisma.books.groupBy({
  by: ["is_published"],
  where: {
    publisher: { location: "Hanoi" },
    category: { title: "science" }
  },
  _count: {
    _all: true
  }
});

Just that and I’m good now 🤝

Result

I fixed the issue and improved the response time by 10 times from ~30s 🔻 ~3s, which is a satisfying result for me.

Before

After

I have created a repository to reproduce this issue. Feel free to take a look or try it out (instructions are in the README file).

Conclusion

Honestly, I've read the code above a few times, but I'm not aware of the problem, even though we've been trying to reduce the API response time before. So I think I’ve learned a lesson from this.

Carefully review the code before think about any fancy stuffs 🙉