1

I need to update / insert a large number of entries very fast. I see 2 options

  • creating many queries and send them via executeBatch
  • create one big query (contains all updates/inserts in db-specific syntax) and just execute it. Since the number of updates is fix ("batch size") i can prepare this statement too

The target db is oracle. The number of inserts/updates in a batch is a fixed number between 1000 and 10000 (does this number has some impact on performance?)

So what way to go?

dermoritz
  • 12,519
  • 25
  • 97
  • 185

1 Answers1

1

Your options are essentially the same. In fact they may be identical, unless your second option is implemented in a poor way.

Using built in PreparedStatement batching is safer, since the driver will know what to do a lot better than you do. There's less chances for programmer error, and should it ever happen that you change your database provider, you won't need to double check whether your solution is still valid.

Make sure to check out how to properly perform the batching. For example the batch size is commonly 100 instead of the full amount of rows you wish to insert (so you would have 10 executeBatch()es to insert your 1000 rows).

Kayaman
  • 72,141
  • 5
  • 83
  • 121
  • thanks, in my case i want to use Apache Camels jdbc component (does not use executeBatch). So this would imply 2nd solution or i have to implement the 1st option on my own not leveraging camel to do the updates. – dermoritz Oct 26 '15 at 09:23
  • Camel is for integration, whereas you were asking about batching. There's a significant difference on the approaches. – Kayaman Oct 26 '15 at 09:42
  • that is why i aksed. at the moment we mixed both approaches. If we would switch to camel sql-component we could use batching through camel (direct support by sql-component). Anyway your answer helped much - saying it is not a question of performance anymore. – dermoritz Oct 26 '15 at 14:46
  • 1
    The options are the same in most cases but the second option will not scale with large batch sizes. As I show in [this answer](http://stackoverflow.com/a/11663076/409172), the common ways to insert multiple rows in one statement will start to slow down after a few hundred. – Jon Heller Oct 27 '15 at 02:29