Forum:

Performance

JDBC executeBatch vs executeUpdate

Greenhorn

Posts: 29

posted 4 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi everyone,

I have a question regarding executebatch vs executeUpdate.
I am wondering how many statements are minimun , acceptable for batching?
I mean is it bad idea to add 15 statements to addbatch?
What is recommended here, I couldnt find any documentation that explains like abour 1000 records should use addBatch , and for less than 20 record, it is better to use .executeUpdate inside a loop.

Thatnk in advance!

Stephan van Hulst

Saloon Keeper

Posts: 15625

366

posted 4 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

I guess the true answer depends on your JDBC implementation, but in general I'd use the the same guideline I'd use for for-loops:

Edsger Dijkstra wrote:2 or more, use a for

I don't see a reason why you couldn't also create a batch containing just two statements.

Björn Björnsen

Greenhorn

Posts: 29

posted 4 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hmm...
So doesnt matter if you use executeUpdate or addBatch -> executeBatch for less than 20 statement.

Stephan van Hulst

Saloon Keeper

Posts: 15625

366

posted 4 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

I'm not saying it doesn't matter at all.

I'm saying you probably already spent more time thinking on the problem than you'll ever save by using one option over the other.

Tim Holloway

Saloon Keeper

Posts: 27871

196

I like...

posted 4 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

I'd venture that a batch submits multiple statements in chunks to the database server, but excuteUpdate takes every statement in the transaction and pushes them all to the server at once. You might see a difference if you're using immediate updates rather than committed ones. But like Stephan said, it's probably not worth agonizing over. I'd batch if I had 10 or more updates in a transaction, as a general rule.

Then again, I'm usually using JPA and I let it worry about when to do which.

The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.

You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.

Campbell Ritchie

Marshal

Posts: 79424

377

posted 4 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

Tim Holloway wrote:I'd venture . . .

We are all guessing. I think Stephan has given a helpful answer:-

. . . you probably already spent more time thinking on the problem than you'll ever save . . .

If you really want to find out, execute each of the instructions 1,000,000× and time them. Repeatedly. in different orders, preceded by lots of the same instructions to make sure all optimisations have time to kick in. Only then do the timing, and you will probably find Stephan is right.
You may find that a database instruction runs more slowly the first time you try it than on subsequent occasions. If so, that probably means the database is indexing the queries and later on those indices allow it to run much faster.

Paul Clapham

Marshal

Posts: 28262

I like...

posted 4 months ago

2
Number of slices to send:

Optional 'thank-you' note:

Send

I've never used executeBatch, so when your question appeared my question was "Why use executeBatch" anyway? Well, the people at Baeldung have writtena tutorial about executeBatch in which they address that question. Their answer is "Performance and data consistency are the primary motives to do batch processing."

Performance, we've covered that already here. Your mileage may vary, as they say. But data consistency: well, you can build a transaction from multiple executeUpdate statements as well, so again the benefits are unclear. The statement repeatedly made is that executeBatch only accesses the database once whereas 100 calls to executeUpdate accesses the database 100 times. Whether that makes any difference is going to depend on your database architecture so, again, try both and see what difference it makes.

Campbell Ritchie

Marshal

Posts: 79424

377

posted 4 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

I would expect that one large access is going to be much faster than hundreds of small accesses.

Tim Holloway

Saloon Keeper

Posts: 27871

196

I like...

posted 4 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

I'll admit to speculation, but I'm pretty sure that there is actual documentation on this that I've read.. And hopefully not garbled too much trying to remember.

I double-checked recent JDBC docs, though and while performance is a thing, one of the features of executeBatch is that it can return multiple result indicators; not to be confused with a ResultSet of database rows. It also requires batch underlying support from the JDBC driver to function.

Note also that there's executeBatch() and then there's executeLargeBatch().

Campbell Ritchie

Marshal

Posts: 79424

377

posted 4 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Tim Holloway wrote:. . . and then there's executeLargeBatch().

. . . whose documentation says it is intended for when the update count exceeds Integer.MAX_VALUE.

Campbell Ritchie

Marshal

Posts: 79424

377

posted 4 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Not that any of those links says anything about performance.