Query with JOIN FETCH performance problem

Question

I have problem with hibernate query performance which I can't figure out. In code snippet below I need select entities with at least one mapping and filtered mapping. I'm using FETCH JOIN for this to load only filtered mappings. But in that case I have performance problems with query. Hibernate says warning log :

org.hibernate.hql.ast.QueryTranslatorImpl - firstResult/maxResults specified with collection fetch; applying in memory!

When I omit FETCH JOIN and left only JOIN query is nice fast. But in result I have all mappings loaded to entity which is not acceptable state for me. Is there a way to boost query performance? There are a lot rows in mapping table.

HQL query :

select distinct e from Entity 
   join fetch e.mappings as mapping 
where e.deleted = 0 and e.mappings is not empty 
   and e = mapping.e and mapping.approval in (:approvals)

Entities :

@Entity
@Table(name="entity")
class Entity {

   ...

   @OneToMany(mappedBy="entity", cascade=CascadeType.REMOVE, fetch=FetchType.LAZY)
   @OrderBy("created")
   private List<Mapping> mappings = new ArrayList<Mapping>();

   ...
}

@Entity
@Table(name="mapping")
class Mapping {

public static enum MappingApproval {
    WAITING, // mapping is waiting for approval
    APPROVED, // mapping was approved
    DECLINED; // mapping was declined
}

...

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="entity_id", nullable=false)
    private Entity entity;

    @Enumerated(EnumType.STRING)
    @Column(name="approval", length=20)
    private MappingApproval approval;

...

}

Thanks

Answer 1

From the JPA-Specifications

The effect of applying setMaxResults or setFirstResult to a query involving fetch joins over collections is undefined. (JPA "Enterprise JavaBeans 3.0, Final Release", Kapitel 3.6.1 Query Interface)

Hibernate does the right thing, but executes a part of the query in memory, which is tremendously slower. In my case the difference is between 3-5 ms to 400-500 ms.

My solution was to implement the paging within the query itself. Works fast with the JOIN FETCH.