Tutorials/Solving the N+1 problem

Solving the N+1 problem

A list query over a lazy relation runs one extra select per row. This page shows where that comes from in JPA, what the standard fixes change, and how Storm decides loading in the entity model, with the SQL each approach runs.

Series · JPA to Storm7 min readKotlin

01The task

Render a list of 50 pets with the last name of each pet's owner. Two tables, one foreign key, no aggregation. One query can answer it. With a lazy relation in JPA, the code below runs 51.

02The JPA way

A typical JPA mapping marks the relation LAZY, so loading a pet does not always load its owner. In Kotlin the class is open (through the all-open compiler plugin), mutable, with nullable defaults, and a plain class rather than a data class:

Pet.kt Kotlin · JPA
1
2
3
4
5
6
7
8
9
10
11
12
// Open (all-open plugin), mutable, nullable defaults: the shape Hibernate expects
@Entity
class Pet(
    @Id @GeneratedValue
    var id: Int? = null,

    var name: String = "",
    var birthDate: LocalDate? = null,

    @ManyToOne(fetch = FetchType.LAZY)
    var owner: Owner? = null,
)

The repository call and the rendering loop:

PetService.kt Kotlin · JPA
1
2
3
4
val pets = petRepository.findAll()   // 1 query
for (pet in pets) {
    render(pet.owner!!.lastName)       // + 1 query per uninitialized proxy
}
↳ generated sql
-- findAll(): one query for the pets ...
SELECT p.id, p.name, p.birth_date, p.owner_id FROM pet p

-- ... then one more per owner while rendering
SELECT o.id, o.first_name, o.last_name FROM owner o WHERE o.id = ?
SELECT o.id, o.first_name, o.last_name FROM owner o WHERE o.id = ?
SELECT o.id, o.first_name, o.last_name FROM owner o WHERE o.id = ?
-- 50 pets of 50 owners: 51 round trips

The list query fetches pets only. Each pet.owner holds a proxy, and the first access to lastName on each proxy runs its own SELECT. The code and the types look the same either way; the query log shows the difference. Switching to EAGER does not help the list case: a JPQL list query still fetches each owner with a follow-up select, only earlier. A proxy first touched after the session has closed throws LazyInitializationException.

03The standard fixes

JPA has two well-known remedies, and they work:

PetRepository.kt Kotlin · JPA
1
2
3
4
5
6
7
8
9
10
interface PetRepository : JpaRepository<Pet, Int> {

    // Fix 1: JOIN FETCH, applied one JPQL query at a time
    @Query("SELECT p FROM Pet p JOIN FETCH p.owner")
    fun findAllWithOwner(): List<Pet>

    // Fix 2: an entity graph, applied one call site at a time
    @EntityGraph(attributePaths = ["owner"])
    override fun findAll(): List<Pet>
}

Hibernate adds batch fetching: @BatchSize on the relation, or hibernate.default_batch_fetch_size for the whole application, loads the proxies in groups with an IN list, so 50 owners take a few queries instead of 50.

JOIN FETCH and entity graphs are written per query or per call site. The entity declares one loading behavior and each query may override it, so a new query that forgets the fetch clause brings the extra selects back. Batch fetching reduces their number but keeps the separate round trips.

The JPA snippets follow Baeldung's guide to the N+1 problem. Within JPA, JOIN FETCH and entity graphs are the right fixes.

04The Storm way

Storm decides loading in the model. A foreign key field declared with the entity type is loaded in the same query, through a join. There is no lazy variant of that field type:

Entities.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
// The field type decides what every read of Pet loads
data class Owner(
    @PK val id: Int = 0,
    val firstName: String,
    val lastName: String,
) : Entity<Int>

data class Pet(
    @PK val id: Int = 0,
    val name: String,
    val birthDate: LocalDate,
    @FK val owner: Owner,   // entity field: joined in the same query
) : Entity<Int>

The same list-and-render code runs one query for every caller:

PetService.kt Kotlin · Storm
1
2
3
4
val pets = petRepository.findAll()   // 1 query, owners included
for (pet in pets) {
    render(pet.owner.lastName)           // plain field access, no database call
}
↳ generated sql
-- findAll() loads the owner graph in the same round trip
SELECT p.id, p.name, p.birth_date, o.id, o.first_name, o.last_name
FROM pet p
INNER JOIN owner o ON p.owner_id = o.id
-- 50 pets of 50 owners: 1 round trip

The result is a list of immutable data classes. pet.owner is a field holding an Owner, not a proxy with a session reference. You can serialize it, cache it or pass it to another thread. Storm has no session, so there is no LazyInitializationException.

The join repeats the owner columns on every row, but the result does not repeat the objects. Entities with the same id in one result share one instance: 50 pets of 20 owners hydrate into 20 Owner objects. Sharing is safe because the objects are immutable.

05When you do not want the join

Some readers never need the owner, and joining it for them is wasted work. In Storm that choice is a field type. Declare the field as Ref<Owner> and Storm reads only the foreign key column. Loading the owner is an explicit call:

Entities.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
data class Pet(
    @PK val id: Int = 0,
    val name: String,
    val birthDate: LocalDate,
    @FK val owner: Ref<Owner>,   // Ref field: reads owner_id, joins nothing
) : Entity<Int>

val pet = petRepository.getById(1)   // no join issued
val owner = pet.owner.fetch()         // loads the owner here, one query
↳ generated sql
-- getById(1): no join, the ref holds the foreign key
SELECT p.id, p.name, p.birth_date, p.owner_id FROM pet p WHERE p.id = ?

-- fetch(): runs only where your code says so
SELECT o.id, o.first_name, o.last_name FROM owner o WHERE o.id = ?

Owner means loaded, Ref<Owner> means not loaded. A loop that calls fetch() per row is an N+1, and the call is in the code where a reviewer sees it. A Ref fetched twice queries once, and inside a transaction fetch() first checks the entity cache, so an owner an earlier read already loaded usually costs no query.

A query whose caller does need the owners says so with fetch(...). The field stays a Ref, and the refs come back loaded from the same statement:

PetService.kt Kotlin · Storm
1
2
3
4
5
6
// A query that needs the owners resolves them in the same statement
val pets = orm.entity(Pet::class).select()
    .fetch(Pet_.owner)
    .resultList

val owner = pets.first().owner.getOrThrow()   // loaded by the query, never queries
↳ generated sql
-- the ref field is unchanged; this query joins the owner
SELECT p.id, p.name, p.birth_date, o.id, o.first_name, o.last_name
FROM pet p
INNER JOIN owner o ON p.owner_id = o.id

Read a resolved ref with getOrThrow(). It never queries, so if someone removes the fetch(...), the code fails at that line instead of turning into one query per row. In development, Storm also reports the loop itself: a transaction that fetches ten or more references of one type one at a time draws repeated-fetch advice in the log, with the call site and the time the fetches took. See Statement advice.

06Prove it in a test

A query count is something a test can assert. Storm's test module captures the SQL a block runs, so a change that adds a query fails the build:

PetQueryTest.kt Kotlin · storm-test
1
2
3
4
5
6
7
8
9
10
11
@StormTest(scripts = ["/schema.sql", "/data.sql"])
class PetQueryTest {

    @Test
    fun `pets and owners load in a single query`(orm: ORMTemplate, capture: SqlCapture) {
        val pets = capture.execute { orm.entity(Pet::class).findAll() }

        pets.forEach { render(it.owner.lastName) }   // walk the graph freely
        capture.count(Operation.SELECT) shouldBe 1
    }
}

@StormTest starts an in-memory H2 database, runs your schema scripts and injects the ORMTemplate and SqlCapture parameters. For Ref fields, capture.count(CapturedSql.Origin.FETCH) counts the statements that resolved a reference one at a time. See Testing for the full setup, including running the same test against PostgreSQL in a container.

07Side by side

JPA with HibernateStorm
Where loading is decidedOn the mapping, overridden per query (JOIN FETCH, entity graphs), or at access time through proxiesIn the entity model: an Owner field joins, a Ref<Owner> field does not; fetch(...) resolves a ref in one query
Lazy relation in a loop1 + N selects, fewer with batch fetchingExtra queries only where fetch() is written
Outside a transactionLazyInitializationException when an untouched proxy outlives its sessionResults are plain values; a ref fetches over its template's connection
Finding an N+1Query log, Hibernate statistics, or a statement-count check in testsrepeated-fetch advice in development; SqlCapture counts in tests

08Keep going

The reference documentation covers the mechanics in depth: