betterprogramming.pub Open in urlscan Pro
162.159.153.4  Public Scan

Form analysis
0 forms found in the DOM

Text Content

Open in app

Sign In

Get started


Home
Notifications
Lists
Stories

--------------------------------------------------------------------------------

Write


Published in

Better Programming

Hernan Reyes
Follow

Jun 15

·
4 min read
·

Listen



Save







HOW THE N+1 QUERY CAN BURN YOUR DATABASE


A BRIEF GUIDE TO HANDLING THIS HARMFUL LOGIC




OVERVIEW

Have you ever watched a movie/series or experienced yourself going to a hotel
where they have room service?

Let’s say you go to one of these hotels, where the hotel’s restaurant is on the
first floor, and you’re hosteling on the tenth floor. Now, when you are there,
you decide to order something to eat for lunch. Imagine that the waitress —
instead of bringing you food with complements (drinks, desserts, etc.) at one
time — brings you every meal, drink, dessert, and more, one by one.

That will be so inefficient because the waitress will have to do many runs
before bringing you everything you asked for. They will need to go back and
forth from floor one to ten. This is what the N+1 problem is, getting everything
you request in many runs.

The ideal will be to carry what you ordered in a bussing cart like this, so the
waitress can bring it all at once.



This article will dive into how this problem looks in code, and offer solutions
you can do to avoid this and ensure the best performance for your application.


TIME TO SEE SOME CODE

To show how the N+1 looks in your code, I’m going to build a simple console
application that prints the available menus to order from a restaurant. For
this, we’re going to have a database with a meals and drinks table. In the menu,
each meal will come with a drink.

Let's see the models for these tables:


Restaurant tables


Now, let’s see the N+1 in action. Here we have some methods to query data from
the database (you can find the full code on my repo):



And finally, here’s the method with the N+1 query problem:



Yes, this simple logic can burn your database, because you are going back and
forth to add the drinks to each meal, which is not efficient.

The more records you have to query or the most users you have, the most this N+1
problem we’ll affect your application because the time complexity is O(N)/Linear
time.

Here I gave you an example in the backend, but this problem can also be found in
your frontend, where instead of calling your database directly, you’ll be
calling an endpoint of your backend which at the same time would call the
database.


SOLUTION

Now, let’s see two solutions to our problem.


JOIN THE AUTHORS IN THE SQL QUERY

This may be the easier solution. In here, you’ll have to write a query like the
following:



With this query, our code will look like the following:



With this query, now we only have you query our database once, and that’s it.


GET THE MEALS AND THEN JOIN THE DRINKS WITH YOUR PROGRAMMING LANGUAGE

No, we won’t do the same as the example where we saw the N+1 problem. Here,
instead of querying the meals and then querying the drinks one by one, we’ll do
two queries in our database. Let’s see how:



As you can see, we only have two queries to our database: s.getMeals() and
s.getDrinksByIDsIn, and if you read the ListMenu method, you’ve noticed that we
introduced two more methods. Let’s see what they do and why we need them:



Now, you can see we don’t query the database for every drink. Instead, in one
query, we get all the meals, and in the other, we query the drinks and then join
them to the corresponding meals.


WHEN TO USE ONE SOLUTION OR THE OTHER?

Well, in this app, every meal includes just one drink, but what happened if a
meal includes more than one drink?

In that scenario, the first solution can’t help us, because the SQL query is
going to repeat the record for every drink in a meal. So what we want to do is
use the second solution when we first query the meals and then get the drinks to
join them to the corresponding meals


PERSONAL EXPERIENCE

At work, we have a microservice that is in charge of caching a lot of data about
the products we have twice a day or on-demand. It used to take about one minute
to cache all the data because of this problem. After we removed the N+1, it went
from one minute to two seconds!


CONCLUSION

Don’t overestimate a simple logic like the N+1. You can fall into this problem
easily, but you can also fix it easily. But if you don’t do it in time, your
application performance will let you know over time.

Something I didn’t mention is the N+1 in ORMs like Gorm. I don’t have experience
with these, but I will recommend if you are using ORMs, that you dig into the
underlying code to see if you have this problem.

Note: The way I structured the code in this article is not meant to be a guide
on how you should structure your code; it is made as simple as possible to focus
on the problem and solution code.


HOMEWORK

If you are working on a project, or you already have projects in production, go
check them out to get rid of any N+1 you find.


REFERENCES

 1. N+1 in Laravel: Here you can see the N+1 in Eloquent (an ORM for Laravel)
 2. Figma Jam and IconDuck: To make the illustrations
 3. Article repository: You can find the examples in my GitHub




302



4



302

302

4




SIGN UP FOR COFFEE BYTES


BY BETTER PROGRAMMING

A newsletter covering the best programming articles published across Medium Take
a look.

By signing up, you will create a Medium account if you don’t already have one.
Review our Privacy Policy for more information about our privacy practices.

Get this newsletter


MORE FROM BETTER PROGRAMMING

Follow

Advice for programmers.

Tài Chí

·Jun 15


HOW ARE YOU STRUCTURING YOUR GO MICROSERVICES?

Here’s my proposed solution based on a real-world project — The term
microservices is undoubtedly the only trend that rocketing these days. Many
companies have transformed their big monolith hard-to-change into many smaller
moving parts named microservices. For that purpose, Golang is definitely one of
the most viable choices at this time. It can help developers quickly build up
tiny…

Go

7 min read





--------------------------------------------------------------------------------

Share your ideas with millions of readers.

Write on Medium

--------------------------------------------------------------------------------

Rodrigo Dutra de Oliveira

·Jun 15


CREATE AND DEPLOY A VUE.JS APP TO GITHUB PAGES

Github pages provides an alternative option to host pages directly from
repositories. — GitHub Pages is a static site hosting service that takes HTML,
CSS, and JavaScript files straight from a repository on GitHub, optionally runs
the files through a build process, and publishes a website. —
https://docs.github.com/en/pages/getting-started-with-github-pages/about-github-pages
Despite being a static file hosting service, we can…

Github Pages

5 min read





--------------------------------------------------------------------------------

applied.math.coding

·Jun 15

Member-only


RUST AS PART OF A MICROSERVICE ARCHITECTURE

Rust + TypeScript — It is already some time ago that I have written a small
account about how to use Rust within a full-stack web application (see here).
This post is intended to follow up on this by offering an alternative approach
in how to include Rust into the application. In general, we…

Rust

6 min read





--------------------------------------------------------------------------------

John Clark Craig

·Jun 14

Member-only


PYTHON SHORTS — ARTIFICIAL GRAVITY IN SPACE

Python makes it simple to calculate just how fast a space station needs to spin
to generate one G of artificial gravity. — In the movie Interstellar, Mathew
McConaughey and crew start their wheel-shaped station spinning in space in order
to generate one G of artificial gravity, or the same acceleration we experience
due to gravity here on Earth. They spin up to a certain rotational velocity in
order for the centripetal force…

Python

4 min read





--------------------------------------------------------------------------------

Pavel Belikov

·Jun 14


A BULLETPROOF API INTEGRATION FOR ANGULAR APPLICATIONS

An Angular-specific way of using adapter pattern — Whenever you are creating the
application UI — one day you face the need to integrate it with API. But what
happens if your API mates bring breaking changes into the contract — it should
not lead to you changing the whole model on…

Angular

2 min read





--------------------------------------------------------------------------------

Read more from Better Programming


RECOMMENDED FROM MEDIUM

Shawn Shivers

THE MAJORITY OF AWAITED OFFERS ARE VERY CLOSE TO YOUNOW! HTTPS://T.CO/AIZW4YTEVY



Buddhika Chathuranga

in

RuntimeError

PATH TO GSOC-2021



Lorin Camargo

in

Code for San Jose

CODE FOR SAN JOSÉ NEWSLETTER — FEBRUARY 2020



Shubham Gupta

in

Ula Engineering

GROWING YOUR SMALL SYSTEMS TO A LARGE DISTRIBUTED SYSTEM IN A RELIABLE WAY:
LESSONS LEARNED HARD…



Ketaki Shivaji Satav

LGMVIP INTERNSHIP BLOG



Sovia Agustina

HMS KITS AND ITS IMPLEMENTATION ON THE WIKIPEDIA APP



Cosmic Exodus

👋 ARE YOU A YAY KNIGHT?



Andrei Diaconu

C# MOCK DIFFERENT HTTPCLIENT CALLS IN THE SAME METHOD



AboutHelpTermsPrivacy

--------------------------------------------------------------------------------


GET THE MEDIUM APP


Get started

Sign In


HERNAN REYES


66 Followers


Hello, I’m a Backend developer from Honduras, currently working remotely and
sharing my knowledge through articles.


Follow



MORE FROM MEDIUM

Hernan Reyes

in

Better Programming

KEEPING TRACK OF DATABASE SCHEMA CHANGES USING GO



peng.yeng

BUILD RETRY-ABLE API USING IDEMPOTENCY KEY



Codejitsu

in

ITNEXT

RATE LIMITING WITH LEAKY BUCKET ALGORITHM



Sanil Khurana

in

Geek Culture

SYSTEM DESIGN SOLUTIONS: WHEN TO USE CASSANDRA AND WHEN NOT TO



Help

Status

Writers

Blog

Careers

Privacy

Terms

About

Knowable

To make Medium work, we log user data. By using Medium, you agree to our Privacy
Policy, including cookie policy.