Performance guide

Sqldelight Performance Settings and Query Tuning

Improve Sqldelight performance with focused queries, indexes, transactions, batching, listener control, pagination and platform-aware testing.

Sqldelight Performance Settings and Query Tuning feature image

Sqldelight performance depends primarily on database design and query behavior, not on generated Kotlin code alone. The generator removes manual mapping, but indexes, transaction boundaries, result size, listener scope and platform driver behavior still determine how quickly an application reads and writes data.

Measure the actual query

Start with a reproducible dataset and the exact generated operation used by the product. Record execution time, returned row count and the query plan. Optimizing a simplified statement can hide the real bottleneck.

Add indexes that match filters and ordering

An index should support the columns used in WHERE, JOIN and ORDER BY clauses. More indexes are not always better because every insert and update must maintain them. Add one, rerun measurements and keep only indexes with a clear benefit.

Reduce result size

Select only required columns, paginate long lists and avoid loading large text or binary values for summary screens. Generated result types make focused projections easy to represent.

Use transactions for grouped writes

Batch related inserts or updates in a transaction. This protects consistency and can reduce repeated disk synchronization. Keep transactions short so they do not block unrelated work.

Illustrative grouped write
database.transaction {
  records.forEach { record ->
    database.recordQueries.insertRecord(
      record.id,
      record.value
    )
  }
}

Avoid N+1 access patterns

Do not execute one related query for every row in a list when a join or batched lookup can return the same information. The generated API is fast to call, but repeated database round trips still cost time.

Control observable query scope

Attach listeners only while a screen or service needs updates. Large result sets that re-execute after every write can create unnecessary work. Prefer smaller queries tied to the data actually shown.

Test each platform driver

Desktop, mobile and browser storage systems have different locking, threading and file behavior. A query tuned on JVM may need different lifecycle or batching choices on another target. Keep a shared benchmark and a small platform-specific performance suite.

Performance checklist

  • Use query plans for slow statements.
  • Index real filter and sort patterns.
  • Return fewer rows and columns.
  • Group related writes in transactions.
  • Remove N+1 loops.
  • Limit listener lifetime and result size.
  • Measure on production-like devices and data.

Related Sqldelight guides