java - Query with JOIN FETCH performance problem -

i have problem hibernate query performance can't figure out. in code snippet below need select entities @ least 1 mapping , filtered mapping. i'm using fetch join load filtered mappings. in case have performance problems query. hibernate says warning log :

org.hibernate.hql.ast.querytranslatorimpl - firstresult/maxresults specified collection fetch; applying in memory!

when omit fetch join , left join query nice fast. in result have mappings loaded entity not acceptable state me. there way boost query performance? there lot rows in mapping table.

hql query :

select distinct e entity     join fetch e.mappings mapping  e.deleted = 0 , e.mappings not empty     , e = mapping.e , 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 waiting approval     approved, // mapping approved     declined; // mapping 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

from jpa-specifications

the effect of applying setmaxresults or setfirstresult query involving fetch joins on collections undefined. (jpa "enterprise javabeans 3.0, final release", kapitel 3.6.1 query interface)

hibernate right thing, executes part of query in memory, tremendously slower. in case difference between 3-5 ms 400-500 ms.

my solution implement paging within query itself. works fast join fetch.