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