How effective is executeBatch on a Prepared Statement?

Question

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.

Solution 1

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.

Solution 2

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.

Solution 3

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.