N 1 Query

Overview

The N+1 query problem happens when the data access framework executed N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.
The larger the value of N, the more queries will be executed, the larger the performance impact. And, unlike the slow query log that can help you find slow running queries, the N+1 issue won’t be spot because each individual additional query runs sufficiently fast to not trigger the slow query log.
The problem is executing a large number of additional queries that, overall, take sufficient time to slow down response time.
Let’s consider we have the following post and post_comments database tables which form a one-to-many table relationship:

If you select the post_comments using this SQL query:

List<Tuple> comments = entityManager.createNativeQuery("""
    SELECT
        pc.id AS id,
        pc.review AS review,
        pc.post_id AS postId
    FROM post_comment pc
    """, Tuple.class)
.getResultList();

And, later, you decide to fetch the associated post title for each post_comment:

for (Tuple comment : comments) {
    String review = (String) comment.get("review");
    Long postId = ((Number) comment.get("postId")).longValue();
    String postTitle = (String) entityManager.createNativeQuery("""
        SELECT
            p.title
        FROM post p
        WHERE p.id = :postId
        """)
    .setParameter("postId", postId)
    .getSingleResult();
    LOGGER.info(
        "The Post '{}' got this review '{}'",
        postTitle,
        review
    );
}

You are going to trigger the N+1 query issue because, instead of one SQL query, you executed 5 (1 + 4).

Solution

Ref: https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89
In real life and in web dev, the solution is pretty much the same: batch elements retrieval when relevant.
As you’ll fetch everything you need in your pantry in a single batch before starting to cook, your code should preload dependent models when needed.

Prevention

cool tools available:

• spring-hibernate-query-utils, a library for Java, makes your tests fail if N+1 queries are detected
• The Database Machine, an ORM for PHP, implements a smart eager loading mechanism
• laravel-query-detector, a laravel package (in PHP), detects N+1 queries on your development environment
• nplusone, a python library for detecting the n+1 queries problem in Python ORMs, including SQLAlchemy, Peewee, and the Django ORM