Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink query optimization problem(Bi-directional OneToOne, redundant SQL selects to the owning side)
Eclipselink query optimization problem [message #633738] Tue, 19 October 2010 08:04 Go to next message
Janne is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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
Re: Eclipselink query optimization problem [message #635015 is a reply to message #634442] Mon, 25 October 2010 12:24 Go to previous message
Janne is currently offline Janne
Messages: 10
Registered: February 2010
Junior Member
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
Previous Topic:Criteria API: left outer join
Next Topic:help with a ORDER BY COUNT (expression builder and reportquery)
Goto Forum:
  


Current Time: Fri Oct 24 18:57:22 GMT 2014

Powered by FUDForum. Page generated in 0.04980 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software