27 November 2025
A while back, I was working on a screen that displayed a list of transactions — maybe a couple thousand rows. The UI was visibly laggy, and the initial load took nearly 400ms on a mid-range device. I profiled it, expecting some layout issue. The bottleneck was the Room query — a simple SELECT * FROM transactions WHERE account_id = ? ORDER BY created_at DESC doing a full table scan because I never added an index on account_id. After adding a single index, the query dropped to under 5ms.
That experience exposed something important. Room hides SQLite from you — you write a DAO interface, annotate it, and Room generates the implementation. But “it just works” is a dangerous place to stay. Room generates real SQL queries that hit real B-tree indexes or do full table scans. Understanding what Room generates and how SQLite plans those queries is the difference between a 2ms query and a 200ms one.
When you write a DAO method like this:
@Dao
interface TransactionDao {
@Query("SELECT * FROM transactions WHERE account_id = :accountId ORDER BY created_at DESC")
fun getTransactions(accountId: Long): Flow<List<TransactionEntity>>
}
Room’s annotation processor generates an implementation class — typically named TransactionDao_Impl. If you’ve never opened the build/generated directory and looked at these files, I’d recommend it. Here’s a simplified version of what Room generates:
// Generated by Room — simplified for clarity
class TransactionDao_Impl(
private val __db: RoomDatabase,
) : TransactionDao {
override fun getTransactions(accountId: Long): Flow<List<TransactionEntity>> {
val sql = "SELECT * FROM transactions WHERE account_id = ? ORDER BY created_at DESC"
val statement = RoomSQLiteQuery.acquire(sql, 1)
statement.bindLong(1, accountId)
return CoroutinesRoom.createFlow(__db, false, arrayOf("transactions"), Callable {
val cursor = DBUtil.query(__db, statement, false, null)
try {
val columnIndexId = CursorUtil.getColumnIndexOrThrow(cursor, "id")
val columnIndexAccountId = CursorUtil.getColumnIndexOrThrow(cursor, "account_id")
val columnIndexAmount = CursorUtil.getColumnIndexOrThrow(cursor, "amount")
val columnIndexCreatedAt = CursorUtil.getColumnIndexOrThrow(cursor, "created_at")
val result = mutableListOf<TransactionEntity>()
while (cursor.moveToNext()) {
result.add(TransactionEntity(
id = cursor.getLong(columnIndexId),
accountId = cursor.getLong(columnIndexAccountId),
amount = cursor.getDouble(columnIndexAmount),
createdAt = cursor.getLong(columnIndexCreatedAt),
))
}
result
} finally {
cursor.close()
}
})
}
}
A few things stand out. Room resolves column indexes by name using CursorUtil.getColumnIndexOrThrow, so renaming a column without updating the query gives you a runtime crash, not a compile error. The cursor is iterated row by row — for 5,000 rows, that’s 5,000 object allocations in a tight loop. And Room wraps this in CoroutinesRoom.createFlow with the table name "transactions", which is how it knows to re-execute when the table changes.
That generated code shows Room converting a raw Cursor into typed objects wrapped in a Flow. Most of the time, that’s what you want. But there are cases where a raw Cursor makes a measurable difference — if you have a one-shot query returning thousands of rows and you only need to aggregate them (sum, count, max), mapping every row into an entity just to loop again is wasteful. A raw SupportSQLiteQuery with cursor access lets you iterate once without intermediate allocations.
The tradeoff is clear — you lose type safety, reactive updates, and you take on cursor lifecycle management yourself. I use raw cursors for heavy one-shot aggregations where allocation pressure matters, and when passing data directly to a CursorAdapter in legacy code. For everything else, Flow<List<Entity>> is the right default because Room handles re-query on data change, cursor closing, and thread safety for you.
Once I started caring about query performance, the next question was: how do I know whether my query is using an index or doing a full table scan? SQLite has EXPLAIN QUERY PLAN, and you can run it directly. I usually run it during development using Android Studio’s Database Inspector or by writing a quick debug utility:
fun debugQueryPlan(db: SupportSQLiteDatabase, sql: String) {
val cursor = db.query("EXPLAIN QUERY PLAN $sql")
while (cursor.moveToNext()) {
val detail = cursor.getString(cursor.getColumnIndex("detail"))
Log.d("QueryPlan", detail)
}
cursor.close()
}
The output tells you how SQLite plans to execute your query. SCAN TABLE means a full table scan — O(n). SEARCH TABLE USING INDEX means an indexed B-tree lookup — O(log n). On 50,000 rows, a full scan might take 80-100ms while an indexed search takes 1-2ms. Multiply that by how often Room re-executes reactive queries, and those milliseconds compound into real jank. There’s also SEARCH TABLE USING COVERING INDEX, where SQLite answers the query entirely from the index without touching the main table — the fastest option.
Running EXPLAIN QUERY PLAN in code works, but Android Studio’s Database Inspector is faster for iterative debugging. With your app on API 26+, open App Inspection > Database Inspector for a live view of every Room database. You can browse tables, run arbitrary SQL, and see results instantly — no code changes needed. I use it constantly to test query plans before writing the DAO method, and to inspect live data for integrity issues.
Here’s the thing about indexes — they’re not a “just add them everywhere” solution. Every index is a separate B-tree that SQLite maintains alongside your data. When you insert, update, or delete a row, SQLite updates every index on that table. More indexes means slower writes and a larger database file.
The basic rule: index columns that appear in WHERE clauses and JOIN conditions. In Room, you do this in the entity annotation:
@Entity(
tableName = "transactions",
indices = [
Index(value = ["account_id"]),
Index(value = ["created_at"]),
]
)
data class TransactionEntity(
@PrimaryKey(autoGenerate = true) val id: Long = 0,
@ColumnInfo(name = "account_id") val accountId: Long,
val amount: Double,
@ColumnInfo(name = "created_at") val createdAt: Long,
)
But single-column indexes aren’t always enough. If your query filters on account_id AND orders by created_at, SQLite might use the index for the WHERE but still need a temporary B-tree to sort results. A composite index on both columns, in the right order, solves this:
@Entity(
tableName = "transactions",
indices = [
Index(value = ["account_id", "created_at"]),
]
)
data class TransactionEntity(
@PrimaryKey(autoGenerate = true) val id: Long = 0,
@ColumnInfo(name = "account_id") val accountId: Long,
val amount: Double,
@ColumnInfo(name = "created_at") val createdAt: Long,
)
The order matters. ["account_id", "created_at"] helps queries filtering by account_id alone, or by both columns. But it won’t help a query filtering only by created_at — SQLite reads composite indexes left to right. Think of a phone book sorted by last name, then first name: you can find all “Jangra” entries quickly, but finding everyone named “Mukul” requires scanning the whole book.
The honest tradeoff: in one of my projects, adding 4 indexes to a frequently-written table increased insert time by about 30%. For a read-heavy table (like a cache of fetched articles), indexes are almost always worth it. For a table that sees constant writes (like an analytics events queue), you need to be more selective.
SQLite by default uses a rollback journal, meaning writes lock the entire database — no reads while a write is in progress. Write-Ahead Logging (WAL) mode changes this by writing changes to a separate log file instead. Readers continue reading from the original database while the writer appends to the WAL file, allowing one writer and multiple readers simultaneously. Room has enabled WAL by default since version 2.0.
The WAL file periodically gets “checkpointed” — merged back into the main database — when it reaches about 1000 pages (~4MB). The tradeoff is disk space: you have the main file, the WAL file, and a shared-memory file (-shm). I’ve seen WAL files grow to 5-10MB on apps doing heavy batch syncing before a checkpoint happens.
This one surprised me the most when I first measured it. Without a wrapping transaction, each insert is its own transaction with its own fsync call — a disk flush with significant latency on Android’s flash storage. Inserting 1,000 rows one-by-one takes 3-5 seconds; wrapping them in a single transaction drops it to 50-100ms. That’s a 30-50x difference.
Room makes this easy with the @Transaction annotation:
@Dao
interface TransactionDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertAll(transactions: List<TransactionEntity>)
@Transaction
suspend fun replaceAllForAccount(accountId: Long, transactions: List<TransactionEntity>) {
deleteByAccount(accountId)
insertAll(transactions)
}
@Query("DELETE FROM transactions WHERE account_id = :accountId")
suspend fun deleteByAccount(accountId: Long)
}
Without @Transaction on replaceAllForAccount, the delete and insert would be separate transactions — a crash between them means an account with no transactions. The annotation wraps everything in BEGIN IMMEDIATE … COMMIT. Room does wrap list inserts in a transaction automatically when you pass a List to @Insert, but calling a single-item @Insert in a loop means each call is its own transaction.
I’ve seen production code where a sync operation called a single-row insert inside a forEach loop. Changing it to collect all items first and call the list-based insert once dropped the sync time from 8 seconds to 200ms.
This is the piece of Room that most developers don’t understand. When you return a Flow from a DAO, Room needs to know when to re-execute the query. It does this through the InvalidationTracker — shadow tables with SQLite triggers that flag when a table is modified. The InvalidationTracker periodically checks these flags and tells every active Flow observing that table to re-execute.
The hidden cost: invalidation is table-level, not row-level. If a Flow watches SELECT * FROM transactions WHERE account_id = 42 and someone inserts for account_id 99, your Flow still re-executes. Room only knows the transactions table was modified. For high write frequency apps, this causes unnecessary re-queries across every Flow touching that table.
It gets worse — invalidation fires on any write, even if data didn’t change. An @Insert(onConflict = REPLACE) with identical data still triggers all Flows to re-execute. In one project, a background sync upserted the same data every 30 seconds, triggering re-queries for 6 Flows on screen — all doing cursor iteration and allocation for nothing. The fix was to diff data before writing.
The InvalidationTracker issue gets particularly nasty with large datasets. If your query returns thousands of rows into a Flow<List<Entity>>, you pay for full cursor iteration and object allocation on every table invalidation. Room integrates with Paging 3 through PagingSource, and it genuinely earns its complexity.
When you return a PagingSource from a DAO, Room generates a LimitOffsetPagingSource that only loads the visible page — say, 30 rows at a time via SELECT ... LIMIT 30 OFFSET ?. Here’s what it looks like:
@Dao
interface TransactionDao {
@Query("SELECT * FROM transactions WHERE account_id = :accountId ORDER BY created_at DESC")
fun getTransactionsPaged(accountId: Long): PagingSource<Int, TransactionEntity>
}
// In your ViewModel
class TransactionViewModel(
private val transactionDao: TransactionDao,
private val accountId: Long,
) : ViewModel() {
val transactions: Flow<PagingData<TransactionEntity>> = Pager(
config = PagingConfig(
pageSize = 30,
prefetchDistance = 10,
enablePlaceholders = false,
),
pagingSourceFactory = { transactionDao.getTransactionsPaged(accountId) }
).flow.cachedIn(viewModelScope)
}
Room ties the PagingSource into the InvalidationTracker, so when the table is modified, the current PagingSource is invalidated and the Pager creates a fresh one. Paginated queries still react to data changes, but each page only loads 30 rows instead of the full dataset. On a table with 50,000 transactions, that’s the difference between allocating 50,000 objects and allocating 30.
Beyond the fundamentals, there are patterns I’ve found useful in production that don’t get talked about enough.
In one project, a transaction list needed the account name alongside each transaction. The normalized JOIN added 15-20ms per query on 20,000 rows. Since account names rarely change, I denormalized — stored account_name directly on the entity. Reads dropped to 3ms. Writes now update two places on rename, but for a read-heavy screen refreshing on every invalidation, the tradeoff was worth it.
Room 2.4 introduced multi-map return types, letting you express one-to-many relationships directly in a query without a separate data class. Instead of a TransactionWithAccount wrapper, return a Map from the DAO:
@Dao
interface TransactionDao {
@Query("""
SELECT accounts.name AS accountName,
transactions.*
FROM transactions
INNER JOIN accounts ON transactions.account_id = accounts.id
WHERE transactions.created_at > :since
ORDER BY transactions.created_at DESC
""")
fun getRecentWithAccounts(since: Long): Flow<Map<String, List<TransactionEntity>>>
}
Room generates the grouping logic for you, which eliminates boilerplate and keeps the relationship logic in SQL where it belongs. I reach for this pattern whenever I need grouped data for a sectioned list — it’s cleaner than doing the grouping in Kotlin after the query returns.
I want to talk briefly about SQLDelight, because IMO it solves a genuine problem Room doesn’t address. In Room, you write SQL inside @Query annotation strings. Room validates these at compile time to some extent, but malformed queries, type mismatches in complex joins, and certain type converter edge cases only surface at runtime.
SQLDelight takes the opposite approach — you write SQL in .sq files, the compiler parses it fully and validates against your schema. A typo or non-existent column fails the build, not the app.
-- transactions.sq
CREATE TABLE transactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_id INTEGER NOT NULL,
amount REAL NOT NULL,
created_at INTEGER NOT NULL
);
CREATE INDEX idx_transactions_account ON transactions(account_id, created_at);
getByAccount:
SELECT * FROM transactions
WHERE account_id = :accountId
ORDER BY created_at DESC;
SQLDelight also gives you full access to SQLite features that Room abstracts away — window functions, recursive CTEs, partial indexes. But the tradeoff is real: steeper learning curve, smaller community, and Room’s Jetpack integration (Paging, Hilt) is more seamless. Room is the right default for most Android apps. SQLDelight earns its keep when you have genuinely complex SQL, when you’re doing Kotlin Multiplatform, or when compile-time query safety is a priority.
The performance optimizations I’ve described aren’t theoretical — they’re things I’ve applied in production apps. If I had to summarize the highest-impact changes:
The gap between “Room works fine” and “Room works well” is about understanding what happens one layer below your DAO interface — in the generated code, in SQLite’s query planner, and in Room’s invalidation system. Once you see that layer, you stop guessing about performance and start reasoning about it.
Thanks for reading!