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.

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