Java – how to speed up the batch operation of Oracle normal statements?

I am developing tools to continuously export changes from mongodb to Oracle database

I have a problem executing batch operations (Oracle)

static void save(List result) {
    withBatchConnection { Statement stm ->
        result.each { String line ->
                stm.addBatch(line)
        }
    }
}

static withConnection(Closure closure) {
    def conn = null
    boolean success = false
    while (!success) {
        try {
            conn = getConnection()
            closure.call(conn)
            success = true
        } catch (e) {
            log.error('Connection problem',e)
            log.error(e,e)
            log.info('retrying for 30 sec')
            sleep(30000)
        } finally {
            conn?.close()
        }
    }
}

static withTransactionConnection(Closure closure) {
    withConnection { sql sql ->
        OracleConnection conn = sql.getConnection() as OracleConnection
        conn.setAutoCommit(false)
        closure.call(conn)
        conn.commit()
    }
}

static withBatchConnection(Closure closure) {
    withTransactionConnection { Connection conn ->
        def statement = conn.createStatement()
        closure.call(statement)
        statement.executeBatch()
        statement.close()
    }
}

The problem is that I can't use prepared statements because the order of operations is very important

When I use rewrite batched statements to save to MySQL, it operates 10K times per second 400 operations for Oracle

Is there a chance to make it faster?

I'm using ojdbc 7 and groovy 2.4 seven

Solution

Please set the array size from client to maximum and try again