Hi All
I have a query , when ever i iam getting 400 -500 records it is taking 3-4 sec. but when iam getting around 5000 record it is taking more time around 3 min in local system.

the method is
public List getAllSourcedCandiadtes(String startingDate, String endingDate) {

return (List) getHibernateTemplate().find(

"select distinct prospectiveCandidates from ProspectiveCandidates prospectiveCandidates where prospectiveCandidates.appliedDate between '" + startingDate + "' and '" + endingDate + "' and (prospectiveCandidates.sourceName != 'candidate' ) and (prospectiveCandidates.sourceResume !=null )");
}

the query is working fine but I want to improve the performance, even i tried using criteria API
Criteria crit = session.createCriteria(ProspectiveCandidates.class)
.add(Expression.between("appliedDate", new Date(startDate.getTime()),new Date(endDate.getTime())));

crit.add(Restrictions.and(Restrictions.ne("sourceName","candidate"), Restrictions.isNotNull("sourceResume")));

List abcd= crit.list();

return abcd;

I am able to get records with criteria API but iam not able to improve the performance.
please suggest me ................

I can help you, if you post the Entity codes with its mapping-metadata,

- How long do the statements take if you run then against the database with a "normal" SQL-Client? Is there a larger difference as well?
- Check if your Java application is having enough memory. Try -verbose:gc to see what the garbage collector does. Try to start with more memory and recheck.

we have used XDoclet to generate hbm files. iam sending u the entity.

import java.util.Date;
import org.apache.commons.lang.builder.EqualsBuilder;
import org.apache.commons.lang.builder.HashCodeBuilder;

/**
* @hibernate.class table="TProspectiveCandidates"
*/
public class ProspectiveCandidates
{
private static final long serialVersionUID = 1L;
private String prospectiveCandidatesId;
private Date appliedDate; // candidate was added as a PC
private Date interviewDate;// date on interview sheduled in the feture
private Date submissionDate;// on which submited to the clients
private Date onBoardDate;// on wich the candidate joined at clien
// location
private Date joiningDate;// on wich the candidate will be joining in the
// feture
private String status;
private String comments;
private Position position;
private JobApplicant jobApplicant;
private SourceResume sourceResume;
private String sourceName;
private Integer version;
private String techUserName;

/**
* @hibernate.property column="tech_UserName" length="50"
* unsaved-value="null"
* @return
*/

public String getTechUserName() {
return techUserName;
}

public void setTechUserName(String techUserName) {
this.techUserName = techUserName;
}

/**
* @return Returns the id.
* @hibernate.id column="prospectiveCandidates_Id" length="32"
* unsaved-value="null" generator-class="uuid.hex"
*/
public String getProspectiveCandidatesId() {
return prospectiveCandidatesId;
}

/**
* @param candidateManagementId
* The candidateManagementId to set.
*/

public void setProspectiveCandidatesId(String prospectiveCandidatesId) {
this.prospectiveCandidatesId = prospectiveCandidatesId;
}

/**
* @hibernate.property column="comments" length="512" unsaved-value="null"
*/

public String getComments() {
return comments;
}

/**
* @param comments
* The comments to set.
*/

public void setComments(String comments) {
this.comments = comments;
}

/**
* @param jobApplicant
* The jobApplicant to set.
* @hibernate.many-to-one column ="jobApplicant_id" cascade="none" class
* ="com.infistech.eservices.apps.hr.model.JobApplicant"
*/

public JobApplicant getJobApplicant() {
return jobApplicant;
}

/**
* @return Returns the jobApplicant.
*
*/
public void setJobApplicant(JobApplicant jobApplicant) {
this.jobApplicant = jobApplicant;
}

/**
* @param position
* The position to set.
* @hibernate.many-to-one column ="position_id" cascade="none"
* class="com.infistech.eservices.apps.hr.model.Position"
*/

public Position getPosition() {
return position;
}

/**
* @return Returns the position.
*
*/
public void setPosition(Position position) {
this.position = position;
}

/**
* @hibernate.property column="status" length="256" unsaved-value="null"
*/

public String getStatus() {
return status;
}

/**
* @param status
* The status to set.
*/
public void setStatus(String status) {
this.status = status;
}

/**
* @hibernate.property column="appliedDate" length="40" unsaved-value="null"
*/

public Date getAppliedDate() {
return appliedDate;
}

/**
* @param appliedDate
* The appliedDate to set.
*/
public void setAppliedDate(Date appliedDate) {
this.appliedDate = appliedDate;
}

/**
* @param sourceResume
* The sourceResume to set.
* @hibernate.many-to-one column ="sourceResume_id" cascade="none"
* class="com.infistech.eservices.apps.hr.model.SourceResume"
*/

public SourceResume getSourceResume() {
return sourceResume;
}

public void setSourceResume(SourceResume sourceResume) {
this.sourceResume = sourceResume;
}

/**
* @hibernate.property column="source_Name" length="20" unsaved-value="null"
* @return
*/

public String getSourceName() {
return sourceName;
}

public void setSourceName(String sourceName) {
this.sourceName = sourceName;
}

/**
* @hibernate.version column="VERSION"
*
* @return
*/

public Integer getVersion() {
return version;
}

public void setVersion(Integer version) {
this.version = version;
}

/**
* @hibernate.property column="interviewDate" length="40"
* unsaved-value="null"
*/
public Date getInterviewDate() {
return interviewDate;
}

public void setInterviewDate(Date interviewDate) {
this.interviewDate = interviewDate;
}

/**
* @see java.lang.Object#equals(Object)
*/
public boolean equals(Object object) {
if (!(object instanceof ProspectiveCandidates)) {
return false;
}
ProspectiveCandidates rhs = (ProspectiveCandidates) object;
return new EqualsBuilder().appendSuper(super.equals(object)).append(
this.comments, rhs.comments).append(
this.prospectiveCandidatesId, rhs.prospectiveCandidatesId)
.append(this.status, rhs.status).append(this.appliedDate,
rhs.appliedDate).isEquals();
}

/**
* @see java.lang.Object#hashCode()
*/
public int hashCode() {
return new HashCodeBuilder(-1251626999, -1741046323).appendSuper(
super.hashCode()).append(this.comments).append(
this.prospectiveCandidatesId).append(this.status).append(
this.appliedDate).toHashCode();
}

/**
* @hibernate.property column="onBoardDate" length="40" unsaved-value="null"
*/

public Date getOnBoardDate() {
return onBoardDate;
}

public void setOnBoardDate(Date onBoardDate) {
this.onBoardDate = onBoardDate;
}

/**
* @hibernate.property column="submissionDate" length="40"
* unsaved-value="null"
*/
public Date getSubmissionDate() {
return submissionDate;
}

public void setSubmissionDate(Date submissionDate) {
this.submissionDate = submissionDate;
}

/**
* @hibernate.property column="joiningDate" length="40" unsaved-value="null"
*/
public Date getJoiningDate() {
return joiningDate;
}

public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}

}

It takes only 0.77sec when i have run the query in mysql. I got 5422 records.

Have you tried with some 'left join fetch' on your many-to-one entities?



Christophe

Thank you christopher

I tried using this left join , but i cant see any change in the timings,
If i use left join then the sql query generated is also taking more time in sql client..
please suggest me some other ideas.