SQLDelight slow performance compared to Room

Question

We want to migrate our App from Room to SQLDelight to reuse it on iOS (it is a Multiplatform project). I noticed however that SQLDelight is much slower than Room. For some queries it is around 10 times slower. I did some in depth testing with insert statements which take ~5ms on average with Room and ~25ms on average with SQLDelight. I checked (and adapted) the query so that they are exactly identical.

I also checked some PRAGMAs and found that SQLDelight uses Pragma SYNCHRONOUS=1 (NORMAL) whereas Room uses 2 (FULL) and SQLDelight uses JOURNAL_MODE=TRUNCATE whereas Room uses WAL. I changed these settings in the SQLDelight setup to be equal to Room and the performance got a bit better but still ~20ms for the test described above.

Does anyone have an idea what causes the difference? Any help is appreciated.

Can you post your code? Benchmarks very much depend on all the details. — Kevin Galligan, Dec 23 '20 at 14:04
Sorry I can't post it because it's for a customer. But I just now found out that it seems to be the journal mode indeed. It's just not working to set it to WAL in driver onOpen callback, maybe it's overwritten later... — Benjamin Bisinger, Dec 23 '20 at 14:08
I don't know off hand on the android driver, but on iOS you very much need to set the journal mode with config and not directly because it needs to happen in a specific order. — Kevin Galligan, Dec 23 '20 at 14:10
Ok thanks! I try to figure out where the right place is for Android. It seems that it doesn't work in onConfigure either, it's always reset to truncate. I'll update the question if I can't figure it out myself. — Benjamin Bisinger, Dec 23 '20 at 14:15
You have the synchronous values reversed. 1 is NORMAL and 2 is FULL https://www.sqlite.org/pragma.html#pragma_synchronous — Sean, Aug 15 '21 at 11:48
@Sean thanks for pointing out, I fixed the question. The answer still applies. — Benjamin Bisinger, Aug 16 '21 at 14:24
Interesting though... 1 should be faster than 2 then. Maybe it's all about the journal mode. — Benjamin Bisinger, Aug 16 '21 at 14:26

Benjamin Bisinger · Accepted Answer · 2020-12-23T15:35:48.237

The reason for the slower performance seems to be the journal mode and synchronous settings indeed. I didn't recognise this before because my changes didn't work the intended way.

So the current answer for me is to set journal mode to WAL and synchronous to 2 which are the defaults for Room. I could only accomplish it by using the following code in the DriverFactory, if someone has a cleaner solution I'm happy to see it.

actual class DriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(
            schema = Database.Schema,
            context = context,
            name = "Database.db",
            callback = object : AndroidSqliteDriver.Callback(Database.Schema) {
                override fun onConfigure(db: SupportSQLiteDatabase) {
                    super.onConfigure(db)
                    setPragma(db, "JOURNAL_MODE = WAL")
                    setPragma(db, "SYNCHRONOUS = 2")
                }

                private fun setPragma( db: SupportSQLiteDatabase, pragma: String) {
                    val cursor = db.query("PRAGMA $pragma")
                    cursor.moveToFirst()
                    cursor.close()
                }
            }
        )
    }
}

You can use `db.enableWriteAheadLogging()` as well. Also, `cursor.moveToFirst()` isn't needed as it does nothing here. — deej, Aug 07 '22 at 18:15
Are these tweaks still valid on V2 alpha 3? I have some performance issues and not sure if it's due my config or what mentioned above — 4face, Sep 14 '22 at 18:08
Android dev docs suggest using `SYNCHRONOUS = NORMAL` (which is `1`) https://developer.android.com/topic/performance/sqlite-performance-best-practices — Mark, Jun 10 '23 at 10:04

SQLDelight slow performance compared to Room

1 Answers1