Eclipselink query optimization problem [message #633738] |
Tue, 19 October 2010 04:04  |
Eclipse User |
|
|
|
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 04:06] by Moderator
|
|
|
|
Re: Eclipselink query optimization problem [message #635015 is a reply to message #634442] |
Mon, 25 October 2010 08:24  |
Eclipse User |
|
|
|
Hi,
I thought I had already tried setting the FETCH hint for "r.company.employee", even though it sounded perverse to me, but after retrying it now, it *does* solve the problem after all! I get the results with just one SQL select now.
Thanks for the help!
Janne
|
|
|
Powered by
FUDForum. Page generated in 0.04407 seconds