N+1 Query¶

Rule details


Rule ID	`n-plus-one-query`
Severity	WARNING — likely performance problem
Confidence	HIGH (repository/dao target) · MEDIUM (other targets)
Category	Query patterns
Complexity	O(n·IO) → O(1·IO + n)

The problem¶

A single-record fetch — findById(), getById(), a REST call per ID — inside a loop. The first query loads a list of IDs (that's the "1"), then N subsequent queries load each related record one at a time (that's the "+N"). Every iteration pays full network round-trip cost: TCP handshake, query parsing, result serialization, response transfer.

With a local database and 10 records, this takes milliseconds. With a remote database and 10,000 records, it takes minutes. The math is brutal: 10,000 × 5ms network latency = 50 seconds of pure waiting, where a single IN (...) query would take 20ms.

N+1 queries are a common reason web applications slow down after launch. They're hard to notice in dev (local DB, small dataset) but add up quickly in production (remote DB, real data volumes).

Where this shows up¶

In Spring Data services that loop over parent IDs and call findById() for each child
In REST API aggregation layers that call another microservice once per item
In Grails controllers using GORM dynamic finders inside .each {}
In GraphQL resolvers that fetch related entities one by one per parent
In batch jobs that process records sequentially instead of in chunks

The pattern¶

Java (Spring Data)Java (JPA)Groovy (GORM)JavaScript (fetch)

for (Long orderId : orderIds) {
    Order order = orderRepository.findById(orderId) // (1)!
        .orElseThrow();
    results.add(process(order));
}

DB round-trip per iteration — 1,000 IDs = 1,000 queries

for (Long orderId : orderIds) {
    Order order = entityManager.find(Order.class, orderId); // DB call per iteration
    results.add(process(order));
}

orderIds.each { id ->
    def order = Order.findById(id) // DB call per iteration
    results.add(process(order))
}

for (const id of orderIds) {
    const res = await fetch(`/api/orders/${id}`); // HTTP call per iteration
    results.push(await res.json());
}

The fix¶

Bulk fetch with Spring Data¶

findAllByIdMap for random accessCustom @Query with IN

List<Order> orders = orderRepository.findAllById(orderIds); // single query
for (Order order : orders) {
    results.add(process(order));
}

Map<Long, Order> orderMap = orderRepository.findAllById(orderIds)
    .stream()
    .collect(Collectors.toMap(Order::getId, Function.identity())); // O(n) build

for (Long orderId : orderIds) {
    Order order = orderMap.get(orderId); // O(1) lookup, preserves original order
    if (order != null) results.add(process(order));
}

@Query("SELECT o FROM Order o WHERE o.id IN :ids")
List<Order> findByIds(@Param("ids") Collection<Long> ids);

Bulk fetch with JPA ¶

TypedQuery<Order> query = entityManager.createQuery(
    "SELECT o FROM Order o WHERE o.id IN :ids", Order.class);
query.setParameter("ids", orderIds);
List<Order> orders = query.getResultList(); // single query

Batch fetch in JavaScript¶

const res = await fetch('/api/orders/batch', {
    method: 'POST',
    body: JSON.stringify({ ids: orderIds }),
});
const orders = await res.json(); // single request

Or with Promise.all when a batch endpoint doesn't exist:

const orders = await Promise.all(
    orderIds.map(id => fetch(`/api/orders/${id}`).then(r => r.json()))
); // parallel, not sequential

Suggestion variants¶

Freeform

Bulk fetch all needed records before the loop, or build an in-memory Map

The suggestion text varies based on the specific call pattern detected. For repository calls, it might mention findAllById; for generic service calls, it gives broader advice.

When to ignore this¶

If the loop processes records one at a time on purpose — for example, because each record requires its own transaction boundary, or because loading all records at once would exceed memory — suppressing is legitimate. Same if the "loop" actually processes at most 2-3 items by design.

// algorilla:ignore n-plus-one-query — each order needs its own transaction
for (Long orderId : orderIds) {
    transactionTemplate.execute(status -> {
        Order order = orderRepository.findById(orderId).orElseThrow();
        // ...
    });
}

How detection works¶

Identifies loop constructs (for, while, forEach, each, stream operations)
Inside each loop, looks for calls matching repository fetch patterns:
- Method names: findById, getById, findOne, getOne, loadById, countBy*
- Compound patterns: getOrderByOrderId, findPaymentByTransactionRef
Checks target variable names for repository indicators (repository, dao, store, service, client)
Cross-method resolution catches hidden repository calls in helper methods

Worth investigating

N+1 queries tend to be high-confidence findings — the pattern is structurally clear. If Algorilla flags one, it's usually worth a look.

I/O cluster — different patterns, same root cause (too many I/O round-trips):

IO In Loop — HTTP, file, and generic network calls in loops
Lazy Loading In Loop — ORM lazy-loaded associations triggered per iteration
Bulk Load for Single Lookup — the inverse problem: loading all records when only one is needed

Language-specific examples:

Java — Spring Data JPA findById in a loop
Groovy — GORM dynamic finders in .each {}

Configuration¶

Disable globally:

rules:
  n-plus-one-query:
    enabled: false

Suppress inline:

// algorilla:ignore n-plus-one-query
for (Long id : ids) {
    Order order = orderRepository.findById(id).orElseThrow();
}