Code Review · Java · Database Performance

Code Review: N+1 Database Query Pattern

Review backend code that performs one database query for a list, then one additional query for each item in the list.

Code Review Exercise

Review the following service method. The code looks simple and may even return the correct result, but there is a serious production performance concern hidden inside it.

java
public List<UserSummary> getUserSummaries() {
    List<User> users = userRepository.findAll();
    List<UserSummary> summaries = new ArrayList<>();

    for (User user : users) {
        List<Order> orders = orderRepository.findByUserId(user.getId());

        summaries.add(new UserSummary(
            user.getId(),
            user.getName(),
            orders.size()
        ));
    }

    return summaries;
}

What concerns do you see before scrolling further?

Review Findings

Issue #1: N+1 query pattern

The method first runs one query to load all users, then runs one additional query for each user to load that user's orders. If there are 100 users, this can become 101 database queries.

Issue #2: The code is correct but may not scale

This is why N+1 bugs are easy to miss in code review. The method can return the right answer in development, but become slow in production when the number of users grows.

Issue #3: Database work is hidden inside the loop

A reviewer should be suspicious of repository, DAO, ORM, or API calls inside loops. That pattern often means the code is doing repeated remote or database work one item at a time.

Issue #4: Latency grows with the number of rows

The method does not just process more data as the input grows. It also performs more database round trips. Network latency, connection pool pressure, and database load can all increase.

Why This Is Not Java-Specific

This example uses Java-style repositories, but the concern is not specific to Java. N+1 query problems appear in many backend stacks and ORM frameworks.

  • Java with Hibernate or JPA
  • Spring Data repositories
  • Python with Django ORM
  • Python with SQLAlchemy
  • Ruby on Rails with Active Record
  • .NET with Entity Framework
  • Any service that performs database calls inside a loop

The language changes, but the review smell is the same: one query loads a parent list, then each parent triggers another query for its related data.

Why This Happens

Developers often write the most direct code first: load users, loop through users, load orders for each user. That feels natural, and it is easy to understand.

The problem is that application code and database access do not have the same cost model. A simple-looking method can hide many database round trips.

1 query  -> load all users
N queries -> load orders for each user

Total: 1 + N queries

Improved Direction: Fetch the Data in Bulk

The exact fix depends on the framework, data model, and business need. The general idea is to fetch related data in bulk instead of one row at a time.

java
public List<UserSummary> getUserSummaries() {
    List<User> users = userRepository.findAll();
    List<Long> userIds = users.stream()
                              .map(User::getId)
                              .toList();

    Map<Long, Long> orderCountsByUserId =
        orderRepository.countOrdersGroupedByUserId(userIds);

    return users.stream()
        .map(user -> new UserSummary(
            user.getId(),
            user.getName(),
            orderCountsByUserId.getOrDefault(user.getId(), 0L)
        ))
        .toList();
}

This avoids one query per user. Instead, the code asks the database for the related information in bulk.

Other Possible Fixes

  • Use a join query when you need related records together.
  • Use a projection or DTO query when you only need summary data.
  • Use JOIN FETCH or entity graphs in JPA when appropriate.
  • Use batching when fetching related data in groups is acceptable.
  • Use framework-specific eager loading carefully, such as select_related or prefetch_related in Django.

The best fix is not always "eager load everything." The best fix is to fetch the data needed for this use case with a predictable number of queries.

What Interviewers Expect

A strong reviewer does not only ask, "Does the code work?" They also ask, "What happens when the data grows?"

  • Correctness: the output may be right.
  • Performance: query count grows with the number of users.
  • Scalability: database round trips can dominate runtime.
  • Design: data access should match the shape of the use case.
  • Observability: logs or metrics should make query count visible.

Interview Follow-Ups

Is N+1 always caused by an ORM?

No. ORMs make the pattern common, but any code that performs one query to load a list and then one query per item can create the same problem.

Is eager loading always the answer?

No. Eager loading can over-fetch data. The better answer is to fetch the data needed for the use case with a predictable and reasonable number of queries.

How would you detect this in production?

Look at SQL logs, query counts per request, database latency, APM traces, and endpoints where response time grows sharply as row count increases.

Why is this considered a senior-level review issue?

Because the code often looks clean and returns correct results. The problem is only obvious if the reviewer thinks about production data size and database round trips.