title: Query with JOIN FETCH performance problem
body: <p>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 : </p>
  
  <blockquote>
    <p>org.hibernate.hql.ast.QueryTranslatorImpl
    - firstResult/maxResults specified with collection fetch; applying in
    memory!</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>HQL query :</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Entities :</p>
  
  <pre><code>@Entity
  @Table(name="entity")
  class Entity {
  
     ...
  
     @OneToMany(mappedBy="entity", cascade=CascadeType.REMOVE, fetch=FetchType.LAZY)
     @OrderBy("created")
     private List&lt;Mapping&gt; mappings = new ArrayList&lt;Mapping&gt;();
  
     ...
  }
  
  @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;
  
  ...
  
  }
  </code></pre>
  
  <p>Thanks</p>