Query with JOIN FETCH performance problem.
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
Query with JOIN FETCH performance problem.
===

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