Hibernate bulk update takes for ever to complete (uses HT_ )

marcelstoer · **Joined:** Fri Nov 06, 2009 6:05 am **Posts:** 12

`Article` entity is a sub-class of the `Product` entity. The inheritance strategy for them is `joined`. `Article#flag` is a boolean attribute which I want to set false for all articles. Hence, I do

Code:

Query query = entityManager.createQuery("update Article set flag=:flagValue");
query.setParameter("flagValue", false);
query.executeUpdate();

I expected this to lead to a single SQL statement against the database which should complete fairly quickly - at least it does if I run the update against the database directly. Instead, Hibernate populates a temporary HT_ table and runs an in-query ie. the update later:

Code:

insert into HT_article select article0_.id as id from schema.article article0_ inner join schema.product article0_1_ on article0_.id=article0_1_.id
    
update schema.article set flag=0 where (id) IN (select id from HT_article)

The actual update statement takes "forever" to complete and locks the affected articles thereby causing lock exceptions in other transactions. By forever I mean more than an hour for 130000 articles.

What's the explanation for this behavior and how could I solve it? Other than running a native query I mean...

marcelstoer · **Joined:** Fri Nov 06, 2009 6:05 am **Posts:** 12

It's worth mentioning that also

Code:

Query query = entityManage.createQuery("update Article set flag=false where articleNumber in (:articleNumbers)");
query.setParameter("articleNumbers", articleNumbers);

leads to the same terribly slow processing using the HT_article table.

stliu · **Joined:** Tue Aug 11, 2009 2:26 am **Posts:** 29

read this http://in.relation.to/Bloggers/MultitableBulkOperations please,

if it does not help, give me a test case to reproduce the performance problem

zds · **Joined:** Wed Oct 17, 2012 7:40 am **Posts:** 3

My problem here is that the query generated by Hibernate is very, very slow on MySQL. It runs for ~8 minutes and then times out.

Here I'm dry running it. 5061 rows in the test set.

1) Populating the temp table, at 0.05 this is not a problem:
insert into ht_k3_stop (select id from k3_stop where location_fk = 44534);

2a) But then what Hibernate generates is similar to this:
update k3_stop set location_fk=53842 where (id) IN (select id from HT_k3_stop);

And this runs for minutes and timeouts in the end.

2b) However, this finishes in 0.26 seconds and per my understanding, does exactly the same:
update k3_stop s, ht_k3_stop t set s.location_fk=53842 where s.id = t.id;

In other words: the "IN" operator used by Hibernate when referencing the temporary table seems to drag down MySQL completely.

Digging deeper, the culprit seems to be the lack of index on the temporary table. If I define the "id" column as the primary key, the query 2a completes in 1.16 seconds.