6

Subject to this question, asks it all:How effective is executeBatch method? Is there a performance benchmark, that says.. 'if you have 1000 records to be inserted, using a executeBatch instead of executeUpdate saves you x amount of database cycles?'

Or Is this just a convention?

EDIT: Here is what I am working with: a DB2 V 8.1 hosted on Z/OS, a web app that would be inserting 80,000 records at one go in it's worst case scenario execution.

Jay
  • 2,394
  • 11
  • 54
  • 98
  • 2
    Rather than have everyone give you woolly numbers, why not try it for yourself and measure it? Only you can know how much benefit you'll get from your set-up. – skaffman Jul 17 '09 at 12:52
  • 1
    @skaffman I don't want to do what is popularly called premature optimization. I would however, like to understand how significantly faster it is. – Jay Jul 17 '09 at 12:56
  • I understand that, but you can't make an informed choice without a bit of benchmarking. – skaffman Jul 17 '09 at 13:04
  • Consider using rewriteBatchedStatements=true https://stackoverflow.com/questions/26307760/mysql-and-jdbc-with-rewritebatchedstatements-true – Roland Oct 23 '19 at 12:23

5 Answers5

10

Not sure what database you are using. When I ran a test on this using db2 this is what I saw:

To write to the database:

1 insert it took 2500 microseconds.

10 inserts it took 6000 microseconds. (600 microseconds per write)

10000 inserts it took about 1 million microseconds. ( 100 microseconds per write)

Performance maxed out there. All this means is that there is a huge overhead in sending messages, and using a batch method minimizes this. Of course, sending inserts/updates in huge batches runs the risk of losing them if the application crashes.

Also of note: Exact numbers will vary depending on your DB and settings. So you will have to find your own "sweet spot." But this gives you an idea.

Alex
  • 6,843
  • 10
  • 52
  • 71
  • @windfinder did you use executeBatch for all the above? And, you said 'huge batches run risk of being lost in application crashes' - if I am using transaction management - I should be good in this scenario? – Jay Jul 17 '09 at 13:02
  • Yes, I used executeBatch. Transaction management will not completely eliminate this risk, but it might reduce it. – Alex Jul 17 '09 at 13:19
  • In the simplest of ways, if I were to set autocommit to false and commit only when the executeBatch returns, How am I at risk? – Jay Jul 17 '09 at 13:42
  • @windfinder - how did you find and measure those times ? – sweet dreams Aug 14 '12 at 07:47
  • Can we get the same performance improvement if we use a callable statement but with the procedure having only the insert statement ? – kaushalpranav Jun 08 '18 at 08:27
1

I'm not sure what you're asking, but for inserting many thousands of rows, a batched statement is hugely faster. I can't give you numbers, though.

skaffman
  • 398,947
  • 96
  • 818
  • 769
  • @skaffman that was my question - how hugely faster is it? Can you give me an approximation? – Jay Jul 17 '09 at 12:49
  • I'd estimate somewhere between 10 and 20 times, it would depend to a large extent on the database itself. A non-batched statement involves a lot more network traffic, so a remove server would benefit more from batching than a local one, for example. – skaffman Jul 17 '09 at 12:52
  • 1
    More than 10-20x in my experience (for my particular use case). – Jack Leow Jul 17 '09 at 12:53
1

In my experience, it is significantly faster - even if you are inserting/updating just a few records at a time. If you are doing more than one update, I would almost always recommend batching them if it makes sense.

That said, you'd have to do some actual testing to figure out the performance improvement for your particular situation.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
0

JDBC specification Chapter 14 says that submitting multiple SQL statements, instead of individually, can greatly improve performance

Evgeniy Dorofeev
  • 133,369
  • 30
  • 199
  • 275
0

In the batchExecute() update statements are taking more time then insert statements I am tring with 5001 insert and update statements ration of performance is 15:84

Kartheek
  • 1