Database - Informix
ORM done to be compatible to JPA using Hibernate3(hibernate core + hibernate annotations + hibernate entitymanager)

Issue: When I use LIKE keyword in the query , it takes three times more time comparing to exsisting in-house ORM solution to return the result.

Example:
List<PersonStudent> lis = em5.createQuery("select p from PersonStudent p " + "where p.familyNameUpper like :sn").setParameter("sn", "%NOYE%").getResultList();

This query returns 20 rows.

How the response time can be improved?


Entity class:

package example;

import java.io.Serializable;
import javax.persistence.*;
import java.sql.Date;
import java.util.*;

@Entity
@NamedQueries({
@NamedQuery(
name = "findPersonStudentByFamilyName",
query = "SELECT p FROM PersonStudent p WHERE p.familyNameUpper LIKE :familyName
"
),
@NamedQuery(
name = "findPersonStudentEnrolledInACourseInAYear",
query = "SELECT p FROM PersonStudent p JOIN p.studCseYrPer s" +
" WHERE s.studCseYrPerId.studyYear = :studyYear" +
" AND s.studCseYrPerId.cseId = :courseId")
})
@Table(name="VUSISv3:PersonStudent")
public class PersonStudent implements Serializable{
private String vuId;
private String title;
private String familyName;
private String familyNameUpper;
private String firstName;
private String secondName;
.................................................

private Set<PersonStudentAddress> psAddresses =
new HashSet<PersonStudentAddress>();
private Set<StudCseYrPer> studCseYrPer =
new HashSet<StudCseYrPer>();

@Id
@Column(name="studid")
public String getVuId() {
return vuId;
}

public void setVuId(String vuId) {
this.vuId = vuId;

}
.............................................
@OneToMany
@JoinColumn(name="studid")
public Set<PersonStudentAddress> getPsAddresses() {
return psAddresses;
}

@OneToMany
@JoinColumn(name="studid")
public Set<StudCseYrPer> getStudCseYrPer() {
return studCseYrPer;
}
.......................................
}


-------------------------------------------------------------------------------

EntityManager em5 = emf.createEntityManager();
EntityTransaction tx5 = em5.getTransaction();
List<PersonStudent> lis = em5.createQuery("select p from PersonStudent p " +
"where p.familyNameUpper like :sn").setParameter("sn", "%NOYE%").getResultList();
....................................

-Siva

I am desperate to find the answer for this issue as we need that to make some critical decisions. Anyone , please provide me with some ideas.....


[quote="suthan99"]Database - Informix
ORM done to be compatible to JPA using Hibernate3(hibernate core + hibernate annotations + hibernate entitymanager)

Issue: When I use LIKE keyword in the query , it takes three times more time comparing to exsisting in-house ORM solution to return the result.

Example:
List<PersonStudent> lis = em5.createQuery("select p from PersonStudent p " + "where p.familyNameUpper like :sn").setParameter("sn", "%NOYE%").getResultList();

This query returns 20 rows.

How the response time can be improved?


Entity class:

package example;

import java.io.Serializable;
import javax.persistence.*;
import java.sql.Date;
import java.util.*;

@Entity
@NamedQueries({
@NamedQuery(
name = "findPersonStudentByFamilyName",
query = "SELECT p FROM PersonStudent p WHERE p.familyNameUpper LIKE :familyName
"
),
@NamedQuery(
name = "findPersonStudentEnrolledInACourseInAYear",
query = "SELECT p FROM PersonStudent p JOIN p.studCseYrPer s" +
" WHERE s.studCseYrPerId.studyYear = :studyYear" +
" AND s.studCseYrPerId.cseId = :courseId")
})
@Table(name="VUSISv3:PersonStudent")
public class PersonStudent implements Serializable{
private String vuId;
private String title;
private String familyName;
private String familyNameUpper;
private String firstName;
private String secondName;
.................................................

private Set<PersonStudentAddress> psAddresses =
new HashSet<PersonStudentAddress>();
private Set<StudCseYrPer> studCseYrPer =
new HashSet<StudCseYrPer>();

@Id
@Column(name="studid")
public String getVuId() {
return vuId;
}

public void setVuId(String vuId) {
this.vuId = vuId;

}
.............................................
@OneToMany
@JoinColumn(name="studid")
public Set<PersonStudentAddress> getPsAddresses() {
return psAddresses;
}

@OneToMany
@JoinColumn(name="studid")
public Set<StudCseYrPer> getStudCseYrPer() {
return studCseYrPer;
}
.......................................
}


-------------------------------------------------------------------------------

EntityManager em5 = emf.createEntityManager();
EntityTransaction tx5 = em5.getTransaction();
List<PersonStudent> lis = em5.createQuery("select p from PersonStudent p " +
"where p.familyNameUpper like :sn").setParameter("sn", "%NOYE%").getResultList();
....................................

-Siva[/quote]

Does the SQL generated query look the same?
If yes, it's probably because the Informix driver or your connection provider goes crazy on prepared statements with like: hibernate always use a porepared statement, maybe your home solution does not.