Order of magnitude insert and select performance fluctuation on android

[dodgog]

Hi!

I'm not sure if it's a bug or a discussion, I'm sorry if this is not the right place to write about this. After profiling database inserts and selects, I'm getting some worrying variation in the results.

I can share the code if needed, but I'm timing individual inserts and selects of one row into and from a database with a 1000 entries. Between benchmarks I DELETE FROM the table.

It seems to start quick, around 600μs and 400μs per individual insert and select, comparable with how the device performs in an android app using SQLDelight (drift behaves two times slower). After a couple of trials it becomes 10 times slower, but then (1 in 20 tries) performs as in the beginning again. I'm attaching dart CPU profiler data I managed to capture of these two behaviors: first is slow, the second is fast.

dart_devtools_2025-05-01_17_45_07.337.json dart_devtools_2025-05-01_17_44_21.850.json

I'm wondering: is this known behavior?

For more reference, here is a repo where I'm benchmarking sqldelight and drift and as an experiment to eliminate usage concerns I'm replacing the database backend in drift with a jni to sqldelight.

Thank you!

[simolus3]

Sorry for the slow response here, I think an issue is the right channel to report this though.

Just for my own understanding, is the slowdown observed with your JNI bridge implementation? So looking at your repo, calling the native methods works fine without drift but then there's a apparent slowdown when introducing drift as a higher layer ontop of them?

[dodgog]

Sorry, I think I wasn't clear. The JNI examples use the AOSP sqlite, which is consistently performant. I contrast this with drift's or sqlite3's unstable behavior, which is anywhere from 1.5 to 10 times slower. I cannot figure out where the slowdown is coming from in the drift database: main thread, database isolate, or from other complications. Do you have an intuition for how much slower sqlite3 is through dart than native sqlite? Are there known performance-limiting patterns in drift on android?

Edit: I am realizing that perhaps this is connected to https://github.com/simolus3/sqlite3.dart/issues/296