[2.0] Bulk storing eBeans

[Mauri]

Hi,

i am using play 2.0 and want to save a huge amounts (up 10 millions) of ebeans in my database after getting their properties from a csv-File.
I've tried something like

while (lines in csv-file left){
  assign values to myModel;
  myModel.save();
}

This process is unfortunatedly slow, as I suppose the database to do issue a single insert + a commit for every single model-object. I am looking for a way to do a bulk store like:

while (lines in csv-file left){
  assign values to myModel;
  myList.add(myModel);
}
Model.save(myList);

Does anyone know of such a possibility? I found this: http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/Ebean.html#save%28java.util.Collection%29 but it seems like this is not supported in play.db.ebean.model ?

Thanks for your support!

[Kevin Bosman]

> Does anyone know of such a possibility? I found this:
> http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/Ebean.html#save%28java.util.Collection%29
> but it seems like this is not supported in play.db.ebean.model ?

Have you tried the following?

ArrayList<MyModel> myList = new ArrayList<MyModel>();

while (lines in csv-file left){
assign values to myModel;
myList.add(myModel);
}

Ebean.save(myList);

[Mauri]

Hi Kevin,

thanks for your help! That did it!

Sadly this doesn't improve the speed significantly. I'm still at about 22 inserted objects per second.
Maybe I should wait and see what happens once my application is connected to the productive Oracle DB.

Is the H2 DB (actually not working as in memory DB) known to be that slow?
By the way: each inserted object has the size of roughly 40 Bytes.

yours
- Mauri

[Kevin Bosman]

> Sadly this doesn't improve the speed significantly. I'm still at about 22
> inserted objects per second.

The problem is probably due to the round-trip delay - you're waiting
for each insert to finish before sending the next.
You should try to split the process up into multiple async threads, so
you're waiting on multiple inserts at any one point in time.
Then it's just a case of finding the optimal number of threads for
your use case...

[Kevin Bosman]

> Sadly this doesn't improve the speed significantly. I'm still at about 22
> inserted objects per second.

Ebean also has a transaction batching mode, whereby it gathers a
configurable number of sql statements before sending them all together
in a batch (as opposed to sending each statement individually).

This is a fairly transparent process, so once you've set up a batch,
you should be able to interact with your play/ebean model the same way
you normally do. So in your case, set up the batch, save each item
normally as you add it, then when all done, simply commit and end the
transaction.

Note that I haven't tried this yet - but it is something I'm going to
be looking further into soon.

http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/Transaction.html#setBatchMode%28boolean%29