SQLite adapter: performance issues during insert operations

[lehvolk]

I have an application that doing large number of inserts during startup. CPU snapshot shows that about 10% of time consumed by creating SQLExcpetions based on missed columns in ResultSet returned by JDBC driver.

Preconditions
Having this as dependencies

    implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.39.2.1'

    api group: 'org.jetbrains.exposed', name: 'exposed-core', version: '0.39.2'
    api group: 'org.jetbrains.exposed', name: 'exposed-dao', version: '0.39.2'
    api group: 'org.jetbrains.exposed', name: 'exposed-jdbc', version: '0.39.2'

Having dummy scheme:

object Locations : IntIdTable() {
    val path = varchar("path", length = 1024)
    val runtime = bool("runtime").default(false)
    val outdated = reference("outdated_id", Locations.id).nullable()
}

and code:

Locations.insertAndGetId {
    it[path] = "smth"
}

This code produces 4 SQLExceptions with message no such column: 'id' and no such column: 'id', no such column: 'path'. no such column: 'runtime' and no such column: 'outdated'

As I can see in debug after insert driver returns result set for query select last_insert_rowid() and Exposed tries to restore entity with it.

[Tapac]

Do you create tables with Exposed or you use existing table? Can you share CREATE TABLE statement for your Locations db?

[lehvolk]

Do you create tables with Exposed.

Yes. Tables created with SchemaUtils. As a result SQL query is produced by SchemaUtils.

[Tapac]

Do you have any SQLite specific parameters set to your connection?

[lehvolk]

Do you have any SQLite specific parameters set to your connection?

No. Something like this:

        val config = SQLiteConfig().also {
            it.setSynchronous(SQLiteConfig.SynchronousMode.OFF)
            it.setJournalMode(SQLiteConfig.JournalMode.OFF)
            it.setPageSize(32_768)
            it.setCacheSize(-8_000)
        }
        if (location == null) {
            val url = "jdbc:sqlite:file:jcdb-${UUID.randomUUID()}?mode=memory&cache=shared&rewriteBatchedStatements=true"
            dataSource = SQLiteDataSource(config).also {
                it.url = url
            }
        } else {
           dataSource = SQLiteDataSource(config).also {
                it.url = url
            }
        }

[joc-a]

Hey @lehvolk. I tried to reproduce this issue with your setup but couldn't. Could you please verify that it still happens in the latest Exposed version (0.43.0 at the time of writing)?

I have an application that doing large number of inserts during startup.

How large?