Tutorials/Exposed vs Storm: eager loading

Eager loading and N+1

Exposed batches lazy references with one call, .with(), made at each call site. Storm decides loading once, in the model, and every query follows it.

Series · Exposed to Storm5 min readKotlin

01The task

Render 50 pets with the last name of each pet's owner. Two tables, one foreign key. The question is when the owner loads.

02The Exposed way

Exposed's DAO layer models the relation with a delegated property. The reference is lazy by design and loads when first read:

Pet.kt Kotlin · Exposed DAO
1
2
3
4
5
6
7
8
9
10
object Pets : IntIdTable("pet") {
    val name = varchar("name", 30)
    val owner = reference("owner_id", Owners)
}

class Pet(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Pet>(Pets)
    var name by Pets.name
    var owner by Owner referencedOn Pets.owner   // loads lazily, on first read
}

Read in a loop, each reference loads with its own query. The remedy is .with(), which loads all the references in one IN query:

FeedService.kt Kotlin · Exposed DAO
1
2
3
4
5
6
7
8
9
10
// Lazy by default: each owner loads when first read
val lines = transaction {
    Pet.all().map { "${it.name} belongs to ${it.owner.lastName}" }   // 1 + N
}

// The fix: eager loading, added at this call site
val fixed = transaction {
    Pet.all().with(Pet::owner)
        .map { "${it.name} belongs to ${it.owner.lastName}" }
}
↳ generated sql
-- without with(): one query per owner on first read
SELECT ... FROM pet
SELECT ... FROM owner WHERE owner.id = ?
SELECT ... FROM owner WHERE owner.id = ?
-- ...

-- with(Pet::owner): the references batch into a second query
SELECT ... FROM pet
SELECT ... FROM owner WHERE owner.id IN (?, ?, ?)

The mechanism is efficient. The decision sits at the call site: each query that reads the relation has to add .with(), and a query without it looks almost the same as one with it, while one runs two statements and the other 1 + N. References loaded this way are cached in the transaction that loaded them, so results that leave the transaction need their data read first.

03The Storm way

Storm decides in the model. A relation declared with the entity type is joined in the same query, for every caller, and the same call produces the same SQL each time:

FeedService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
// The loading policy is declared once, in the model
data class Pet(
    @PK val id: Int = 0,
    val name: String,
    @FK val owner: Owner,   // entity field: joined in the same query
) : Entity<Int>

val lines = orm.entity(Pet::class).findAll()
    .map { "${it.name} belongs to ${it.owner.lastName}" }   // no database access in the loop
↳ generated sql
-- one query, for every caller
SELECT p.id, p.name, o.id, o.first_name, o.last_name
FROM pet p
INNER JOIN owner o ON p.owner_id = o.id

There is no eager-loading call to add, because the field is not lazy. Where deferring the load is the right choice, that is also a field type:

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

val pet = orm.entity(Pet::class).getById(1)
val owner = pet.owner.fetch()   // loads the owner here, one query

A loop that fetches a Ref per row is an N+1 you can see in the code. A query that needs the owners resolves them in the same statement with select().fetch(Pet_.owner), and in development the advice log reports a loop of single fetches (Statement advice). The JPA edition of this tutorial covers both in more depth, and query counts are one-line assertions with SqlCapture.

04Side by side

Exposed DAOStorm
DefaultLazy; each reference loads on first readJoined in the same query
Eager loading.with() per call site, one batched IN queryThe field type: an entity field is always joined
Choosing lazinessThe defaultA type: Ref<T>, loaded by fetch() or by the query's fetch(...)
Result scopeLoaded references are cached in their transactionPlain values, usable anywhere

05Keep going

The reference documentation covers the mechanics in depth: