Relations, transactions and N+1 queries

Data and storage Mid-level Kotlin 2.0+ API 24+ Reviewed 31 Oct 2026 By Best Latest Apps

Pick the fix

Loading 200 playlists with their songs makes 201 queries. Best fix?

OptionsA PlaylistWithSongs class with @Embedded and @Relation, from a @Transaction DAO methodRun the 200 song queries in parallelCache the songs in memoryIncrease the cursor window size

Hint 1 of 1

How many queries does a relation need?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A A PlaylistWithSongs class with @Embedded and @Relation, from a @Transaction DAO method

Correct. Room loads all songs for those playlists in one extra query.

Why not the others?

B Run the 200 song queries in parallel. Still 200 queries, and SQLite serialises writes.
C Cache the songs in memory. Hides the problem until the cache misses.
D Increase the cursor window size. Doesn't reduce the number of queries.

30-second answer

Model one-to-many relations with a data class that has @Embedded parent and @Relation children; Room loads children in one extra query per relation, not one per parent. Annotate the DAO method with @Transaction so both queries see the same consistent snapshot. Also use @Transaction (or withTransaction) when several writes must succeed or fail together.

Practise saying it · 35 s. No recording, just a timer

Explain relations and transactions in Room.

Say your answer out loud, then check which of these points you covered:

@Embedded + @Relation
Avoids N+1
@Transaction for consistency
Batch writes in one transaction

Deep dive

Without @Transaction, a write between the parent and child queries could leave you with mismatched data.

Many-to-many relations use a cross-reference entity and Junction.

Batch writes inside one transaction are also much faster than separate transactions.

Add indexes on foreign key columns.

A one-to-many relation Compile-checked · Kotlin 2.4.21

@Entity data class Playlist(@PrimaryKey val id: Long, val name: String)
@Entity(indices = [Index("playlistId")])
data class Song(@PrimaryKey val id: Long, val playlistId: Long, val title: String)

data class PlaylistWithSongs(
    @Embedded val playlist: Playlist,
    @Relation(parentColumn = "id", entityColumn = "playlistId") val songs: List<Song>,
)

@Dao interface PlaylistDao {
    @Transaction
    @Query("SELECT * FROM Playlist")
    fun observeAll(): Flow<List<PlaylistWithSongs>>
}

Common mistakes

Looping over parents and querying children one by one.
Forgetting @Transaction on relation queries.
Doing network calls inside a database transaction.

Senior take & follow-ups

Name the N+1 problem and how @Relation solves it.

Follow-up questions

Think of your answer first, then open the hint.

Why is a loop of queries slow?
Show hint
N+1 round trips instead of 2.
What does withTransaction do?
Show hint
Runs a suspend block in one database transaction.

Suggest a fix if something here is wrong or out of date.