Unexpected behavior of InsertOperation.parameters about rxkotlin-jdbc HOT 6 CLOSED

I didn't run this, but I'm pretty sure you are not varargs-ing that argument and it's treating that list as a single argument.

Maybe try passing this to parameters()?

*persons.flatMap { listOf(it.firstName, it.lastName) }.toTypedArray()

Also, why aren't you using the batching feature? That's going to be just as much if not more efficient...

https://github.com/thomasnield/rxkotlin-jdbc/blob/master/README.md#batching

from rxkotlin-jdbc.

You're right. I haven't tested the performance of the batching feature but in the past I have noticed that a transaction which contains many single inserts can be significantly slower than a single insert with multiple rows. I don't know how the internals of the batching feature work, which is why I shy away from it. But I will try it, thanks!

from rxkotlin-jdbc.

I've adapted my code to use the batching feature, but it doesn't play nice with transactions that need to manipulate data from multiple tables (in my case 2 data tables and a junction table connecting them) because the autoCommit property of the Connection is set to true after each call to batchExecute. Also, as I expected, the batching feature is significantly slower (from 1 second to ~ 9 seconds). I'm going back to my own solution for now but as I originally opened the issue because I failed to see the difference between passing varargs and a collection (which you immediately pointed out) I'm leaving the issue closed as that problem is actually resolved.

from rxkotlin-jdbc.

Okay, I'm not quite sure how specific your task is or what PostgreSQL does to implement JDBC batching. But if you see any opportunities to improve the API's flexibility please let me know.

from rxkotlin-jdbc.

Thanks, @thomasnield.
From what I understand, JDBC batching basically uses a transaction to perform many statements in 1 commit. My high-speed alternative makes use of multi-row insert syntax:

INSERT INTO tableX (col1, col2) VALUES (val11, val12), (val21, val22), (val31, val32);

This is, as far as I know, supported in at least PostgreSQL and SQL Server, probably MariaDB too. Perhaps the batching feature could build such a statement, though I doubt it can then use prepared statements because the length of the statement and the amount of parameters depends on the amount of rows to insert in 1 go. In addition, it might very well be vendor specific and not compliant with JDBC standards.

Regarding transactional support, how do you feel about something like this:

fun <T> DataSource.transaction(op: Connection.() -> T): Single<T> {
    this.connection.use {
        val initialAutoCommitValue = it.autoCommit
        it.autoCommit = false
        return try {
            val t = it.op()
            it.commit()
            Single.just(t)
        } catch (e: Exception) {
            it.rollback()
            Single.error(e)
        } finally {
            it.autoCommit = initialAutoCommitValue
        }
    }
}

I used that code, but I didn't test it very extensively...

from rxkotlin-jdbc.

interesting... although that assumes no Observables or other Rx streams will be used in the closure to do the write operations.

I've got a few idioms I've developed at work to do several disparate write operations in one transaction. I'll see if I can dig them up and we can walk through use cases.

from rxkotlin-jdbc.