Hibernate performing too many queries

Question

I believe that I'm misunderstanding how subselect and eager works; my goal is to improve performance as I encounter the N+1 problem

Edit 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?

I've created the following test case to highlight my issue, please excuse the crudity of this model..

@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<EmployeeArea> employeeAreas = new HashSet<EmployeeArea>();
}

@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<EmployeeArea> employeeAreas = new HashSet<EmployeeArea>();
}

@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;
}

I have then populated with some sample test data: -

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);

This can be run a few times to provide some data.

I then perform the following: -

session.createQuery("FROM Employee e INNER JOIN e.employeeAreas ea INNER JOIN ea.area").list();

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

I would expect that it would be doing at most 3 queries and 2 sub queries.

1. SELECT * FROM Employee INNER JOIN EmployeeArea ON condition INNER JOIN Area ON condition
2. SELECT * FROM Employee WHERE employeeId IN (subquery 1)
3. SELECT * FROM Area WHERE areaId IN (subquery 2)

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: -

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
                )
            )

then a very similar one for area.

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.

Answer 1

I solved the problem; it was an issue with my join table. See the following: -

@Id
@ManyToOne
public Employee employee;

@Id
@ManyToOne
public Area area;

I had used @Id which was resulting in the StackOverflowError exception that was being thrown. Using the following query, with a OneToMany fetch of EAGER and @Fetch JOIN on Employee and a OneToMany fetch of LAZY and @Fetch SELECT on Area, I can then perform the following query: -

List<Employee> employees = session.createQuery("FROM Employee e INNER JOIN FETCH e.employeeAreas ea INNER JOIN FETCH ea.area").list();

Whilst being able to use WHERE on the one of the join table columns.