Slow batch insert

[BakaOnigiri]

Hello,

I have a batch insert that try to insert a least 4000 records in a
foreach loop.

And it is quite slow, and can take more than 2 minutes, in a little
test program using only jdbc, it takes only 2 seconds.

I think this is a problem linked to hibernate cache system.

I try to understand what to do after reading this link :
https://forum.hibernate.org/viewtopic.php?p=2374413

But I don't really know what and how to apply cache optimisations.

Can someone help me ?

Thanks.

[Michael Boyd]

I had this problem inserting ~60,000 records regularly. It took too long.

I got around it by generating one big SQL "INSERT" statement and executing that. It now takes about 30 seconds. Maybe you can try this.

-- Michael Boyd

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.

[Kosta]

I had the same issue. It seems there are two ways you can go about
doing this. Both ways involve grabbing the underlying hibernate
session/transaction objects and issuing commit/flush statements at a
predefined interval.

The trickiest part is actually figuring out where to pull the
hibernate session from, and it's done like this:

org.hibernate.Session sess =
(org.hibernate.Session)User.em().getDelegate();
Transaction tx = session.beginTransaction();

Now after you start off the transaction, instead of calling .save() on
your model directly, do this instead:
sess.save(new MyModelObject());

and then after x number of rows commit/flush/clear:
if(i % 100 == 0) {
tx.commit();
session.flush();
session.clear();
tx = session.beginTransaction();
}

Using this I was able to bring down insertion time from ~10minutes
down to about 30 seconds.

Also, if you are using Oracle (sequences) make sure to turn on sql
debugging and check if you are retrieving the sequence for each
insert. You don't generally want this as it will also add additional
overhead. Ideally you should have sequences/inserts grouped together,
not alternating.

Hope that helps.

> > play-framewor...@googlegroups.com<play-framework%2Bunsubscribe@go oglegroups.com>
> > .

[Kosta]

Sorry, just if it wasn't clear in the line where I call User.em() I
copy pasted that bit from my code, User should be whatever your actual
model that you're working with is.

[BakaOnigiri]

Great ! I will test that at work on Monday.

Thanks.

[lu dongping]

hi,all

Batch saving is ok, but updating is not.

Then I change to  saveOrUpdate but with no luck.

I have to use Model.save again,
Now it takes about 30 minutes to save or update 7000+ records  , which is definately unacceptable.

Anyone can help?

Thanks.

To unsubscribe from this group, send email to play-framewor...@googlegroups.com.