2

For bulk insertion we normally prefer BATCH operation. How exactly is it optimized for faster insertion in jdbc ?

Improve this question

1 Answer 1

Reset to default
4

Normally, by reducing network round-trips. If you are going to execute the same statement 100 times with 100 different sets of bind variables, for example, it would be much more efficient to send all 100 sets of bind variables to the database at once and get back all 100 results using a single network round-trip than it would to incur 100 separate network round-trips in order to execute each query sequentially. If you tell the JDBC driver that you want to create a batch, the driver can minimize the number of times it needs to communicate with the database.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Additionally I think that especially the Oracle JDBC driver applies some more optimization because the speed when using batched inserts nearly matches SQL*Loader's speed. But I don't know what exactly makes it that fast.
@a_horse_with_no_name, I am bit curios to know this kind of optimization.Is it a really happens on the database side? what I am trying to know is that say you have two databases say oracle and mysql.If I run the same query in both these database performance will vary.
@a_horse_with_no_name - I could potentially see the Oracle JDBC driver doing a direct-path load if you have a batch of INSERT statements but that would be hard to do transparently. You should be able to get conventional path SQL*Loader-like performance just by reducing network round trips and bulk binding the bind variables.
When using the batch API, even if you make batches of size 1, it's still way faster. So there's definitely some extra optimization going on, beyond network roundtrip

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.