| Eclipselink query optimization problem [message #633738] |
Tue, 19 October 2010 08:04  |
 Janne
Messages: 10
Registered: February 2010 |
Junior Member |
|
|
Hi,
Apologies for a possible cross post - also sent this to the eclipselink-users mailing list, but it hasn't yet appeared on the list archive a day after posting, so posting here as well.
I'm using criteria API to select n entities, say Employee objects, that have a bi-directional OneToOne relationship with a Company entity. I'm having trouble optimizing the query to use a single SQL select.
Without adding any hints to the query, Eclipselink first executes a select to the Employee table, then one additional select to the Company table per employee row, then yet one additional select per employee row to the employee table, resulting in 2n + 1 selects.
I thought that I could solve the problem by adding a JOIN or FETCH hint to the query, but it only got rid of half of the problem. After adding a QueryHints.FETCH for employee.company property, Eclipselink now does generate a proper joined SQL select to select both Employees and Companies in one query, but then still executes additional one select per employee/company row to the Employee table, as if it wouldn't understand that the @OneToOne mapping on the Company's side refers to the same Employee object that it just used in the join, resulting in n + 1 selects.
Below is a simplified example of the entities, the query and the generated SQL. I hope I haven't omitted anything relevant. Anyone see any obvious problems with the code? Why the seemingly redundant select queries to the Employee table?
Also, if I change the Employee entity so that the company_uuid column becomes the primary key and change JoinColumn annotation to PrimaryKeyJoinColumn, the query executes as a single SQL select. This would solve the problem, but another project that uses Hibernate is dependent on these Entities, and there seems to be a problem with Hibernate and PrimaryKeyJoinColumn I'll look into this more if I can't find another solution.
I'm using Eclipselink 2.1.1 and PostgreSQL 8.4
Any help appreciated!
Thanks,
Janne
ENTITY CLASSES
==============
@Entity
public class Employee{
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@JoinColumn(name = "company_uuid", referencedColumnName = "uuid", unique = true)
@OneToOne(optional = false)
private Company company;
}
@Entity
public class Company{
@Id
@Column(name = "uuid")
private String uuid;
@OneToOne(mappedBy = "company")
private Employee employee;
}
JPA QUERY
=========
..
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Employee> query = builder.createQuery(Employee.class);
Root<Employee> root = query.from(Employee.class);
root.alias("r");
query.setHint(QueryHints.FETCH, "r.company");
query.orderBy(root.get(Employee_.id));
TypedQuery<Employee> typedQuery = em.createQuery(query);
typedQuery.setFirstResult(10);
typedQuery.setMaxResults(10);
List<Employee> result = typedQuery.getResultList();
..
ECLIPSELINK GENERATED SQL QUERIES
==================================
1. SELECT t1.id, t0.uuid FROM company t0, employee t1 WHERE (t0.uuid = t1.company_uuid)
2. SELECT id FROM employee WHERE (company_uuid = ?)
3. SELECT id FROM employee WHERE (company_uuid = ?)
4. SELECT id FROM employee WHERE (company_uuid = ?)
[Updated on: Tue, 19 October 2010 08:06] Report message to a moderator |
|
|
| Re: Eclipselink query optimization problem [message #634442 is a reply to message #633738] |
Thu, 21 October 2010 17:51  |
|
The issue is that the relationship from Company to Employee is based on the company_uuid foreign key not the Employee primary key, so it cannot obtain a cache hit and must resolve on the database.
If you can change the model, you could add and employee_id foreign key to the Company and use it instead of the mappedBy. This would allow the relationship to resolve based on the Id and get a cache hit.
Another solution is to change the order of your query, instead of querying for Employee, query for Company.
i.e.
Select c from Company c join fetch c.employee
Or you could still query on employee, but join fetch both relationships,
query.setHint(QueryHints.FETCH, "r.company");
query.setHint(QueryHints.FETCH, "r.company.employee");
James : Wiki : Book : Blog : Twitter
|
|
|
|
Powered by
FUDForum. Page generated in 0.02921 seconds
] 
Search
Help
Register
Login
Home
