title: Hibernate performing too many queries
body: <p>I believe that I'm misunderstanding how subselect and eager works; my goal is to improve performance as I encounter the N+1 problem</p>
  
  <p><strong>Edit</strong> I'm wondering whether it would be quicker for me to just use the create SQL query method and create the objects myself although I was hoping hibernate would be on par with performance. I can pull back all of the data required, in the example below, in a single query so why on earth is hibernate doing a separate query for each?</p>
  
  <p>I've created the following test case to highlight my issue, please excuse the crudity of this model..</p>
  
  <pre><code>@Entity
  @Table(name = "Area")
  public class Area implements Serializable
  {
      @Id
      @GeneratedValue(generator = "areaId" )
      @GenericGenerator(name = "areaId", strategy = "uuid2")
      public String areaId;
  
      @OneToMany(mappedBy = "area", fetch=FetchType.EAGER)
      @Fetch(FetchMode.SUBSELECT)
      public Set&lt;EmployeeArea&gt; employeeAreas = new HashSet&lt;EmployeeArea&gt;();
  }
  
  @Entity
  @Table(name = "Employee")
  public class Employee implements Serializable
  {
      @Id
      @GeneratedValue(generator = "employeeId" )
      @GenericGenerator(name = "employeeId", strategy = "uuid2")
      public String employeeId;
  
      @OneToMany(mappedBy = "employee", fetch=FetchType.EAGER)
      @Fetch(FetchMode.SUBSELECT)
      public Set&lt;EmployeeArea&gt; employeeAreas = new HashSet&lt;EmployeeArea&gt;();
  }
  
  @Entity
  @Table(name = "EmployeeArea")
  public class EmployeeArea implements Serializable
  {
      @Id
      @GeneratedValue(generator = "employeeAreaId" )
      @GenericGenerator(name = "employeeAreaId", strategy = "uuid2")
      public String employeeAreaId;
  
      @Id
      @ManyToOne
      public Employee employee;
  
      @Id
      @ManyToOne
      public Area area;
  }
  </code></pre>
  
  <p>I have then populated with some sample test data: -</p>
  
  <pre><code>Employee employee = new Employee();
  Area area = new Area();
  
  EmployeeArea employeeArea = new EmployeeArea();
  employeeArea.area = area;
  employeeArea.employee = employee;
  
  session.save(employee);
  session.save(area);
  session.save(employeeArea);
  </code></pre>
  
  <p>This can be run a few times to provide some data.</p>
  
  <p>I then perform the following: -</p>
  
  <pre><code>session.createQuery("FROM Employee e INNER JOIN e.employeeAreas ea INNER JOIN ea.area").list();
  </code></pre>
  
  <p><em>The reason I do the JOIN is so that I can perform specialist searches. I was looking at criteria but it seemed that it didn't allow me to do all that I could with WHERE</em></p>
  
  <p>I would expect that it would be doing at most 3 queries and 2 sub queries.</p>
  
  <ol>
  <li>SELECT * FROM Employee INNER JOIN EmployeeArea ON condition INNER JOIN Area ON condition</li>
  <li>SELECT * FROM Employee WHERE employeeId IN (subquery 1)</li>
  <li>SELECT * FROM Area WHERE areaId IN (subquery 2)</li>
  </ol>
  
  <p>In fact, for 6 inputs of the test data aforementioned, I seem to be getting 6 selects for an employee, 6 selects for an area, something that looks like my assumed query for '1.' and then two larger queries that seem just plain wrong: -</p>
  
  <blockquote>
  <pre><code>select
      employeear0_.employee_employeeId as employee2_3_2_,
      employeear0_.employeeAreaId as employee1_4_2_,
      employeear0_.employee_employeeId as employee2_4_2_,
      employeear0_.area_areaId as area3_4_2_,
      employeear0_.employeeAreaId as employee1_4_1_,
      employeear0_.employee_employeeId as employee2_4_1_,
      employeear0_.area_areaId as area3_4_1_,
      area1_.areaId as areaId1_0_0_ 
  from
      EmployeeArea employeear0_ 
  inner join
      Area area1_ 
          on employeear0_.area_areaId=area1_.areaId 
  where
      employeear0_.employee_employeeId in (
          select
              employee1_.employeeId 
          from
              EmployeeArea employeear0_ 
          inner join
              Employee employee1_ 
                  on employeear0_.employee_employeeId=employee1_.employeeId 
          where
              employeear0_.area_areaId in (
                  select
                      area2_.areaId 
                  from
                      Employee employee0_ 
                  inner join
                      EmployeeArea employeear1_ 
                          on employee0_.employeeId=employeear1_.employee_employeeId 
                  inner join
                      Area area2_ 
                          on employeear1_.area_areaId=area2_.areaId
                  )
              )
  </code></pre>
  </blockquote>
  
  <p>then a very similar one for area.</p>
  
  <p>My goal is to be able to use each employee object in the returned list to identify the areas worked in. There would be more fields in each entity however this test case has been simplified.</p>