SqlDelight performance compared to Room

159 views Asked by At

I will just repost my question which I have posted on SqlDelight Git Hub, but I am not getting any answers. Maybe I get some info here.

Hello there. I have a question related to the performance of SqlDelight on Android. My use case is that I have to download a large amount of data and store it. I am inserting about 15 000 rows into a table, all 9 columns are defined as INTEGER. I am using 2.0.0 of SqlDelight and for comparison, i am using room, version 2.6.0 Here is the snippet for insertion

Timber.e("XXXX : MAPPED")

RoomDatabase.roomDao().insertAll(*myData)
Timber.e("XXXX : ROOMED")

SqlDelightDatabase.transaction {
    myData.forEach {
        daoSqlDelight.insert(it)
    }
}

Timber.e("XXXX : DELIGHTED")

Here is the output. Also, I am adding 15k rows around 5 times, and the times are more-less the same for every batch insert.

11:25:49.891  E  XXXX : MAPPED
11:25:50.530  E  XXXX : ROOMED // room insertion takes around 650ms
11:25:54.829  E  XXXX : DELIGHTED // sqldelight insertion takes around 4300ms

Here is how I create the SqlDelight DB:

sqldelight {
    databases {
        create("SqlDelightDatabase") {
            packageName.set("com.my.package")
        }
    }
}

 AndroidSqliteDriver(
            schema = SqlDelightDatabase.Schema,
            context = get(),
            name = "somename.db",
            callback = object : AndroidSqliteDriver.Callback(CocktailsStore.Schema) {
                override fun onConfigure(db: SupportSQLiteDatabase) {
                    super.onConfigure(db)
                    setPragma(db, "JOURNAL_MODE = WAL")
                    setPragma(db, "SYNCHRONOUS = NORMAL")
                }

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

I was trying firstly, without the callback = object part, but there is no real performance impart if I specify the callback part.

Here is the Sql.

CREATE TABLE my_table (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    pram1 INTEGER NOT NULL DEFAULT -1,
    pram2 INTEGER NOT NULL DEFAULT -1,
    pram3 INTEGER NOT NULL DEFAULT -1,
    pram4 INTEGER NOT NULL DEFAULT -1,
   // 5 more same int prams
);

insertItem:
INSERT INTO my_table (
    id,
    pram1,
    pram2,
    pram3,
    pram4,
   // more prams
) VALUES (?,?,?,?,?,   ?,?,?,?,?,   ?,?,?);

Can you help me to improve the performance? I would like to stick to SqlDelight as I plan to port my project into KMM in the coming months. Thanks

0

There are 0 answers