1

I have two entities Employee and Department

The Employee entity is a read-only dataset for my application (I don't own this table)

The table data backing the Department Entity I do own. If I query my Employee entity without annotating a Department to Employee entity relationship, the query is superfast because it produces only one SQL call.

As soon as I annotate the Department relationship into the Employee entity, there are a ton of SQL calls. Here are what I think are the pertinent details for each entity to assist in getting help:

@Entity
Employee
@Id
String employee_number;
String first_name;
String last_name;
String phone_number;
@Column(name="dept")
private String home_dept_number;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="dept", referencedColumnName="dept_number", insertable = false, updatable = false)
@NotFound(action = NotFoundAction.IGNORE)
private Department department;


@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="dept", referencedColumnName="department_number", insertable = false, updatable = false)

@Entity    
Department
@Id
String dept_number
String dept_name;

@OneToOne(fetch = FetchType.LAZY)
@NotFound(action=NotFoundAction.IGNORE)
@JoinColumn(name = "department_manager_id", referencedColumnName = "employee_number")
private Employee departmentManager;

@OneToOne(fetch = FetchType.LAZY)
@NotFound(action=NotFoundAction.IGNORE)
@JoinColumn(name = "department_admin_aide_id", referencedColumnName = "employee_number")
private Employee departmentAdminAide;

@OneToOne(fetch = FetchType.LAZY)
@NotFound(action=NotFoundAction.IGNORE)
@JoinColumn(name = "department_rep_id", referencedColumnName = "employee_number")
private Employee departmentRepresentative;
jeff
  • 3,618
  • 9
  • 48
  • 101
  • As a starting point add optional=false to all non-optional single ended associations and see how it looks after that. http://stackoverflow.com/a/222604/1356423 – Alan Hay Feb 16 '15 at 17:15
  • It would help if you told *which* queries are executed (JPQL and SQL). My guess is that the slowness is caused by the NotFound annotations: Hibernate is probably forced to check if the department really exists. Using NotFound is a hack to compensate for an inconsistent database where employees reference departments which don't exist. If your database is consistent (and that should be enforced by foreign key constraints), you don't need those annotations. – JB Nizet Feb 16 '15 at 17:50
  • YES! I was psyched to see you @JBNizet respond. you are the man! That was it. When I removed the @NotFound(action=NotFoundAction.IGNORE) only 1 SQL call was made, even though query result was for numerous employees. Before I'd get the same number of SQL queries as people found. – jeff Feb 16 '15 at 18:01
  • @JBNizet respond as answer and I'll accept. Please comment on the fact that I still have String home_dept_number in the Employee Entity, where as in this post you say its useless. http://stackoverflow.com/questions/15076463/another-repeated-column-in-mapping-for-entity-error/15076546#15076546 I though why load the Department Entity, when I only need the employees department number. – jeff Feb 16 '15 at 18:04
  • If the entity is read-only, that could be OK I guess. Having two different associations mapped on the same column doesn't make much sense, though. – JB Nizet Feb 16 '15 at 18:10

1 Answers1

3

My guess is that the slowness is caused by the @NotFound annotations: Hibernate is probably forced to check if the department really exists.

Using @NotFound is a hack to compensate for an inconsistent database where employees reference departments which don't exist. If your database is consistent (and that should be enforced by foreign key constraints), you don't need those annotations.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • This knowledge was huge. I had this annotation littered all over the place and removing has really improved the performance of my queries. However, it has also broken a few queries. The reason I had used it was like you said: inconsistencies in the database (tables I don't control) as well as other data anomalies (duplicate department numbers http://dba.stackexchange.com/questions/57810/table-design-for-departments-with-duplicate-department-numbers) and employees reporting to an ID which is not part of my read-only employee feed. Should I post a Q for other ways around w/o @NotFound? – jeff Feb 16 '15 at 20:17
  • There's no silver bullet. The best way to deal with such inconsistencies is to remove them, and make sure they don't happen again by adding foreign key constraints. – JB Nizet Feb 16 '15 at 20:52