Hi, I anyone could assist me I would appreciate it.
I am having perfomace issues running a simple query in hibernate. On my localhost it takes about 9 seconds to return about 7000 rows. On a remote server it takes about 3 seconds to return the 7000 rows. When I run the SQL natively through a database client (Toad) it takes about 300ms. This suggests that the time is being spent in the marshalling of the of the result set into java objects.
I have 3 tables:
workitemhistory - n:1 - workitem - n:1 - initiativeThe sql I run is:
Code:
String sql = "SELECT h FROM WorkItemHistory h "+
"WHERE h.workItem.initiative.prn IN :prns "+
"ORDER BY h.dayModified";//, h.workItem.state.group";
Query query = em.createQuery(sql);
query.setParameter("prns", prns);
final List<WorkItemHistory> results = query.getResultList();
return results;
I have also tried the following with the same result:
Code:
String sql = "SELECT h.* FROM workitemhistory h "+
"INNER JOIN workitem w ON w.id = h.workitem_id "+
"INNER JOIN initiative i ON i.id = w.initiative_id AND i.prn IN :prns "+
"ORDER BY h.daymodified, h.stategroup";
Query query = em.createNativeQuery(sql, WorkItemHistory.class);
query.setParameter("prns", prns);
final List<WorkItemHistory> results = query.getResultList();
return results;
My beans are mapped as follows:
WorkItemHistoryCode:
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "workitem_id")
public WorkItem getWorkItem() {
return workItem;
}
WorkItemCode:
@OneToMany(mappedBy = "workItem", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
public List<WorkItemHistory> getWorkItemHistory() {
return workItemHistory;
}
@ManyToOne (fetch = FetchType.EAGER)
@JoinColumn(name = "initiative_id")
public Initiative getInitiative() {
return initiative;
}
InitiativeCode:
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
public Integer getId() {
return id;
}
I have tried Lazy loading of the WorkItemHistory, but need the WorkItems and get a org.hibernate.LazyInitializationException.
Please can anyone advise on how to make this faster? Surely hibernate should not take so long to Marshall serveral thousand rows? If I were to use jdbc and put these in a java object, it would be a lot faster.
Thanks in advance.
Login
FAQ
Search