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.

Relativity

Can you post your code? Benchmarks very much depend on all the details. – Arms

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... – Relativity

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. – Arms

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. – Relativity

You have the synchronous values reversed. 1 is NORMAL and 2 is FULL sqlite.org/pragma.html#pragma_synchronous – Tantalate

@Tantalate thanks for pointing out, I fixed the question. The answer still applies. – Relativity

Interesting though... 1 should be faster than 2 then. Maybe it's all about the journal mode. – Relativity

Answer 1

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()
                }
            }
        )
    }
}

Relativity

iOS default is WAL, just FYI. – Arms

You can use db.enableWriteAheadLogging() as well. Also, cursor.moveToFirst() isn't needed as it does nothing here. – Clan

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 – Aguste

Android dev docs suggest using SYNCHRONOUS = NORMAL (which is 1) developer.android.com/topic/performance/… – Regular