Batching support
Closed this issue ยท 7 comments
Hello, and thanks a lot for your work.
I'd like to replace the following JDBC code:
val updateFlags = conn.prepareStatement("UPDATE table SET field='something' WHERE id = ?")
ids.toSet().forEach { id ->
updateFlags.setLong(1, id)
updateFlags.addBatch()
}
updateFlags.executeBatch()
updateFlags.close()
Which runs decently fast (a few hundred ms for 10k entries)
I naively tried to run:
let now = Instant::now();
let mut stmt = conn.prepare("UPDATE table SET field='something' WHERE id = :1", &[StmtParam::FetchArraySize(10_000)])?;
for id in &ids {
stmt.execute(&[id])?;
}
println!("Time for {} updates: {}", ids.len(), now.elapsed().as_millis());
conn.commit()
But unfortunately it takes way too much time as the number of ids
increase. (More than 10s for 10k entries).
I see in README there's "Batch support" mentioned.
I guess this covers such feature, am I right?
If so, well, thank you!
And could you please notify me by closing this issue whenever you have time to implement it?
This way I would know I can go back to dealing with Oracle in Rust!
Thanks a lot.
The StmtParam::FetchArraySize option is for queries, using it for updates will only increase the amount of memory your program need.
The batch support works, I personally tested the latest version of rust-oracle with a 19c database and performance is quite good,
furthermore, if you put all the logic inside a SQL block and send it with rust-oracle similar to what you would do with a SQL tool where you run your SQL scripts will notice that performance with rust-oracle is better than with a Java thin driver which most SQL tools use.
If summary your code should look like this:
let ids: Vec<i32> = (0..10000).map(|v| v + 1).collect();
let now = Instant::now();
let mut stmt = conn.prepare("UPDATE table SET field='something' WHERE id = :1", &[])?;
for id in &ids {
stmt.execute(&[id])?;
}
println!("Time for {} updates: {}", ids.len(), now.elapsed().as_millis());
conn.commit()
Some numbers: running the above code using two VMs in the same machine, one runing the rust program and the other the DB in a container with 10k records, got 1970 ms, now putting all the logic inside a SQL string block and running it with the same stmt.execute got only 104 ms which is fater than my 145 ms using a SQL tool with Java thin driver.
Hope this help,
R
Thanks for your comment.
I'll give it another try, I must have been doing something wrong with batching.
Sorry for not replying for so long time.
Batching is efficient when the network distance between the client and the server is long.
When a network round trip requires 1ms, inserting 10k rows requires at least 10s excluding time spent in the server side.
If 1000 rows are sent in a batch, it decreases to 10ms.
Well, I'll implement it in two or three weeks.
I added batch support. See the following doc comment. I'll release it after I add notice that batch errors and row counts are available only when both the client and the server are Oracle 12.1 or upper.
Lines 227 to 406 in 0f62963
Rust-oracle 0.5.1 was published. It includes batching support.
Hi @rgutierrez2004 , could you give a bit more detail on "putting all the logic inside a SQL string block" please? Do you mean also including the ID and time stamp generation into the SQL script? If yes, can you share the final script as an example?
Thanks in advance!
Some numbers: running the above code using two VMs in the same machine, one runing the rust program and the other the DB in a container with 10k records, got 1970 ms, now putting all the logic inside a SQL string block and running it with the same stmt.execute got only 104 ms which is fater than my 145 ms using a SQL tool with Java thin driver.
Hope this help,
R