Native query is faster than HQL/Criteria query in

问题:

I have a table which has partition on daily basis.

When I used query through HQL/Criteria, it took 240 sec to get the results in Hibernate. The same query(native query created by Hibernate) took 2sec when I used directly or through JDBC connection.

Note: The native query has taken less than 2 sec from postgres db(direct firing).

Please reply where might be the problem

Here is the Entity with Named Query:

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table(name = "sms_info")
@NamedQueries(value = { @NamedQuery(name = "smsInfoforMsisdn", query = "SELECT si FROM SmsInfo si WHERE msisdn = :msisdn and sendStatus IN (0,5) order by lastSmsTrialTime desc") })
public class SmsInfo implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "sms_id")
    private Integer smsId;
    @Column(name = "msisdn")
    private String msisdn;
    @Column(name = "vertical", length = 30)
    private String vertical;
    @Column(name = "sms_text", length = 1024)
    private String smsText;
    @Column(name = "sms_type", length = 255)
    private String smsType;

    @Column(name = "last_sms_trial_time")
    private Timestamp lastSmsTrialTime;

    @Column(name = "send_status")
    private Integer sendStatus;
    @Column(name = "unique_id")
    private String uniqueId;

    @Column(name = "source_of_generation")
    private String sourceOfGeneration;

    public Integer getSmsId() {
        return smsId;
    }

    public void setSmsId(Integer smsId) {
        this.smsId = smsId;
    }

    public String getMsisdn() {
        return msisdn;
    }

    public void setMsisdn(String msisdn) {
        this.msisdn = msisdn;
    }

    public String getVertical() {
        return vertical;
    }

    public void setVertical(String vertical) {
        this.vertical = vertical;
    }

    public String getSmsText() {
        return smsText;
    }

    public void setSmsText(String smsText) {
        this.smsText = smsText;
    }

    public String getSmsType() {
        return smsType;
    }

    public void setSmsType(String smsType) {
        this.smsType = smsType;
    }

    public Timestamp getLastSmsTrialTime() {
        return lastSmsTrialTime;
    }

    public void setLastSmsTrialTime(Timestamp lastSmsTrialTime) {
        this.lastSmsTrialTime = lastSmsTrialTime;
    }

    public Integer getSendStatus() {
        return sendStatus;
    }

    public void setSendStatus(Integer sendStatus) {
        this.sendStatus = sendStatus;
    }

    public String getUniqueId() {
        return uniqueId;
    }

    public void setUniqueId(String uniqueId) {
        this.uniqueId = uniqueId;
    }

    public void setSourceOfGeneration(String sourceOfGeneration) {
        this.sourceOfGeneration = sourceOfGeneration;
    }

    public String getSourceOfGeneration() {
        return sourceOfGeneration;
    }

    }

First I used Named Query and then the native query as follows:

Query smsinfo = session
                    .createSQLQuery("select smsinfo.sms_id as sms_id, smsinfo.last_sms_trial_time as last_sms_trial_time, smsinfo.msisdn as msisdn, smsinfo.send_status as send_status, smsinfo.sms_text as sms_text, smsinfo.sms_type as sms_type, smsinfo.source_of_generation as source_of_generation, smsinfo.unique_id as unique_id, smsinfo.vertical as vertical from sms_info smsinfo where smsinfo.msisdn=:msisdn and (smsinfo.send_status in (0 , 5)) order by smsinfo.last_sms_trial_time desc").addEntity(SmsInfo.class);

Please let me know if you need anything more.