Slow Hibernate Envers Query to Get Revision for Entity with OneToMany - Stack Overflow

admin管理员组

文章数量:1134605

I have an entity with a OneToMany relationship as a list. I want to get a specific revision of the entity. So that will include a list of other entities. So I'm getting the correct result, however the query is very slow (3s) because it's doing a very complicated query containing max and <=. My native query that returns what I like takes 0.3s.

I have tried the RevisionRepository.findRevision method as well as trying the Audit Reader by doing things like

AuditReaderFactory.get(entityManager)
        .createQuery()
        .forRevisionsOfEntity(TaskDefinitionEntity.class, true, false)
        .add(AuditEntity.id().eq(taskDefinitionId))
        .add(AuditEntity.revisionNumber().eq(revisionId))

The query that Envers creates is this:

select
many_columns    
from pf.task_definition_seeds_revisions t1_0,pf.seed_revisions se1_0 
where 
t1_0.seed_id=se1_0.id 
and t1_0.task_definition_id='ea26372f-63e2-4310-bcbb-0a119a55e0cf' 
and se1_0.rev=
(
select max(se2_0.rev)
from pf.seed_revisions se2_0 
where 
se2_0.rev<='8228'
and se1_0.id=se2_0.id
) 
and t1_0.rev=
(
select max(t2_0.rev) 
from pf.task_definition_seeds_revisions t2_0 
where 
t2_0.rev<='8228'
and t1_0.task_definition_id=t2_0.task_definition_id 
and t1_0.seed_id=t2_0.seed_id 
and t1_0.order_id=t2_0.order_id
) 
and t1_0.rev_type<>'2'
and se1_0.rev_type<>'2';

I do not understand why it would need to include a select max and a lesser-than-equal revision comparison. When I update a main entity then I see the OneToMany entities all get marked as deleted in their revision tables and added again with the same revision as the new main entity revision. So a simple query should be possible. Is there a way to configure envers to create a simpler query for OneToMany relationships? Or should I write a native query?

本文标签： Slow Hibernate Envers Query to Get Revision for Entity with OneToManyStack Overflow