Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

HQL update query on abstract entity generates temporary table

Description

In our model we have an abstract entity (called abstracttaskentity in the provided snippet) with JOINED inheritance policy.

When we try to run an HQL UPDATE query that will update some attributes of this abstract entity with a set of conditions, hibernate split the query in three parts :

  • create a temp table

  • insert all matching records in this table

  • launch update query

This is the generic behaviour but in our case those temporary tables are not needed and have a performance cost.

This issue was discovered because of https://hibernate.atlassian.net/browse/HHH-3326#icft=HHH-3326. There are therefore related but they are not duplicated as this issue is about removing usage of temporary table when they are not needed whereas the upper is about using them correctly when they are needed.

Minimal code to reproduce

Query q = entityManager.createQuery("UPDATE abstracttaskentity e SET " + "e.locked = true " + "WHERE e.locked = false AND e.taskId = :id"); q.setParameter("id", taskToAcquire.getTaskId()); entityManager.getTransaction().commit();

Hibernate query log

Hibernate: create table #abstracttaskentity ( id varchar(255) not null ) Hibernate: insert into #abstracttaskentity select abstractta0_.id as id from abstracttaskentity abstractta0_ where abstractta0_.locked=0 and abstractta0_.id=? Hibernate: update abstracttaskentity set locked=1, where ( id ) IN ( select id from #abstracttaskentity )

I attached a test case

Attachments

1
  • 13 Aug 2020, 12:39 PM

Activity

Show:

Nathan Xu August 21, 2020 at 9:52 PM

Christian Beikov August 17, 2020 at 12:54 PM

I don’t know how hard it is to fix as I wasn’t able to take a deep look yet, but given that we already do some tracking of used fields for optimizing inheritance joins for normal queries, this might be pretty easy to get done.

When I find the time, I will look into this. Until then, if someone else wants to step up, I’m offering to provide guidance and will do the review.

François Dambrine August 17, 2020 at 12:54 PM

Ok, I have not found any ticket so I rephrased this one. Let me know if you need more modifications or data.

Nathan Xu August 17, 2020 at 11:59 AM

Yes, if no similar ticket exists. A good ticket to expose some generic issue. To my knowledge, the fixing is challenging, though.

François Dambrine August 17, 2020 at 7:12 AM

Hello,

After some testing I can tell that :

  • it happens even if I have only one attribute in the where clause (I don’t know why my former logs did not show this)

  • it happens whatever the database I use (I also tested with postgres), the only difference is that collation is the right one on postgres.

Do you want me to rephrase the bug?

Fixed

Details

Assignee

Reporter

Labels

Components

Fix versions

Affects versions

Priority

Created August 13, 2020 at 7:14 AM
Updated May 9, 2022 at 10:30 AM
Resolved August 27, 2020 at 9:21 AM