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.
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.
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.
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.