Optimizing Database Operations in Mobile Apps
CoreData on iOS can be catastrophically slow if using viewContext for heavy queries right in viewDidLoad. NSFetchRequest without fetchBatchSize on 10,000-row table loads all objects in memory instantly — on main thread. On iPad with large database this is 400–600 ms UI block per screen open.
Typical Bottlenecks
Queries without indexes. SQLite under CoreData, Room and most mobile ORM. WHERE on unindexed field on 50,000-row table does full scan. On Android Room — add @Index to entity, on iOS CoreData — set indexed in Data Model Inspector. Speed difference — dozens of times.
N+1 queries. Load order list, then for each — separate query for user. 100 orders = 101 SQLite requests. CoreData solves via relationshipKeyPathsForPrefetching, Room — via @Relation with @Transaction. Flutter + sqflite — JOIN query instead of nested loop.
Writes on main thread. managedObjectContext.save() in iOS, database.insert() in Android — shouldn't happen on main thread with large volumes. One save() of 500 objects on older iPhone 8 — easily 200–300 ms block.
Solutions by Platform
iOS — CoreData
NSPersistentContainer gives newBackgroundContext() for background operations. Correct scheme:
container.performBackgroundTask { context in
// bulk operations here
try? context.save()
DispatchQueue.main.async {
// UI update
}
}
NSFetchRequest.fetchBatchSize = 20 — CoreData loads data in batches as accessed, not all at once. NSFetchedResultsController with sectionNameKeyPath for sectioned tables — correct pattern auto-updating UITableView.
For bulk insert NSBatchInsertRequest (iOS 13+) works directly in SQLite without creating managed objects — 10–20x faster for thousands of records.
Android — Room
@Query with EXPLAIN QUERY PLAN via adb shell — quick way to see if full scan. Room @TypeConverter for JSON fields via Gson / Moshi works but slugs on bulk queries — normalize data.
Flow<List<Entity>> from Room auto-emits new data on table change — no manual cache invalidation. distinctUntilChanged() prevents extra emissions if data unchanged.
Room.databaseBuilder().setQueryCoroutineContext(Dispatchers.IO) — explicitly specify Room queries on IO dispatcher.
Flutter — sqflite / Drift (Moor)
Drift (formerly Moor) — preferred for complex schemas: type-safe queries, migrations, code generation. database.transaction() for batch ops — 1000 INSERT in transaction executes 50–100 ms, without — 5–10 seconds (each INSERT opens/closes SQLite transaction).
Case: Search Over 200,000 Records
Offline product catalog app: search by name on Room without FTS took 1.8 seconds. Connected FTS4:
@Fts4
@Entity(tableName = "products_fts")
data class ProductFts(val name: String, val description: String)
MATCH on FTS table — 40–60 ms on same dataset. Noticeable difference.
Timelines
Query audit and optimization — 2–4 days. Adding indexes, switching to batch ops and background contexts — 3–7 days depending on codebase volume.