HQL query takes to long to execute

Question

I'm knew to hibernate. I have a sql statement

SELECT VERSION_ID,D_BEGIN,D_END, 
 (SELECT NAME FROM zvit where ZVIT_ID=version.ZVIT_ID) as name  
 FROM version  
 where  VERSION_ID in  
 (SELECT term.VERSION_ID FROM term  
 where term.PERIOD_MONTH= :periodMonth and term.PERIOD_TYPE= :periodType and term.PERIOD_YEAR= :periodYear )  
 order by 1 ;

I tried to implement it with createCriteria and HQL.

    DetachedCriteria subQuery  = DetachedCriteria.forClass(TermData.class)
            .add(Restrictions.eq("periodmonth",  periodMonth))
            .add(Restrictions.eq("periodtype",  periodType))
            .add(Restrictions.eq("periodyear", periodYear))
            .setProjection(Projections.projectionList() 
                    .add(Projections.property("versionId.versionId"))
                    );          
    Criteria versionCriteria = session.createCriteria(VersionData.class)
            .addOrder(Order.asc("versionId"))
            .add(Subqueries.propertyIn("versionId", subQuery))
            .createAlias("zvitId", "zvitId", org.hibernate.sql.JoinType.INNER_JOIN)
            .setProjection(Projections.projectionList() 
                    .add(Projections.property("versionId"))
                    .add(Projections.property("dbegin"))
                    .add(Projections.property("dend"))
                    .add(Projections.property("zvitId.name"), "name")

                    );

HQL:

            Query query = session.createQuery(""
                    + "from VersionData as version "
//                  + "inner join version.zvitId as zvit "
                    + "where version.versionId in "
                    + "(select term.versionId from TermData as term "
                    + "where term.periodmonth= :periodmonth and term.periodtype= :periodtype and term.periodyear= :periodyear)");

The problem is that this HQL takes 10 times longer to execute. And does a lot of unnecessary queries. I tried to do it with commented string and it improved a bit, but still works 5 time longer than createCriteria query and in addition I couldn't do this convertion

List<VersionData> queryResult = query.list(); as i get VersionData and ZvitData objects. That doesn't seems a problem, but i still don't get why I don't receive just VersionData Object. But there is more important question is there any way to improve this HQL statement for it to execute about the same time as mysql or createCriteria query.

VersionData defenition

@Entity
@Table(name = "version")
public class VersionData implements Serializable
{

    /**
     * 
     */
    private static final long serialVersionUID = 7355281418627668744L;

    @Id 
    @Column(name="VERSION_ID")  
    private String versionId;   

    /**
     * user dbegin
     */
    @Column(name = "D_BEGIN")
    @Type(type = "date")
    private Date dbegin;

    /** user dend */
    @Column(name = "D_END")
    @Type(type = "date")
    private Date dend;  


    /**
     * user zvitId
     */
    @ManyToOne
    @JoinColumn(name="ZVIT_ID") 
    private ZvitData zvitId;

    @OneToMany(targetEntity=TermData.class, mappedBy = "versionId", cascade=javax.persistence.CascadeType.ALL, fetch=FetchType.LAZY)
    private List<TermData> terms;
//getters, setters, hashcode, equals
}

For every VersionData you have a list of TermData, so you must add here, the definition of TermData. This is import for depth of your main query. — Joe Taras, Mar 04 '16 at 09:24

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

Well, with the criteria query you posted, you only fetch the fields you need, while with your HQL query you fetch the whole Hibernate managed entity (VersionData). Depending on how this entity is configured, you might see then other queries fired to populate the fields of this entity.

You may do the same thing as you do with criteria also with HQL, and in my opinion it would be more understandable and therefore maintainable than working with criteria. Just define a java class MyClass for the fields you wish to fetch, with appropriate constructor, and call an HQL query which looks something like:

"select new path.to.myclass.MyClass ( "
                + " alias1.field1, "
                + " alias2.field2, "
                + " ... "

where aliases are the names you are giving to the entities in your HQL query (e.g. version is the alias of VersionData in the query you wrote). No projections, no extra abstractions, just an SQL-like query.

Instead of defining a new class to hold the fields returned from the HQL query (which I find more convenient), you may fetch the fields directly, then processing the result returned, as in the link below:

How to retrieve only certain fields of an entity in JPQL or HQL? What is the equivalent of ResultSet in JPQL or HQL?

edited May 23 '17 at 11:59

Community

1
1

answered Mar 04 '16 at 08:47

John Donn

1,718
2
19
45

Is that a usual practice to create a new Class for fields you wish to fetch? And if I have a lot of different queries where I don't need to fetch all VersionData model is it really beneficial to make a lot of classes and HQL comparing with using createCriteria or using createQuery with select necessary fields in long term. I have really no experience with maintaining HQL or createCriteria so I wonder what would be better. – Evgen Mar 04 '16 at 09:16
@Evgen You can extend `MyClass` from `VersionData` and fill it using a result transformer. For nested projections you can add some setters. Or you can map nested projections to the `VersionData` directly. It is need a special custom transformer for it. Something like [FluentHibernateResultTransformer](https://github.com/v-ladynev/fluent-hibernate/blob/master/fluent-hibernate-core/src/main/java/com/github/fluent/hibernate/internal/transformer/FluentHibernateResultTransformer.java). – v.ladynev Mar 04 '16 at 09:23
If you don't wish to create a lot of classes, then proceed as in the link indicated. On the other hand, the fields may correspond, for example, to rows in a list one presents to the end user, and in this case I think it is advantageous to have them inside a class. As for the usual practice, I feel that Hibernate encourages developers to use their criteria abstractions instead of using HQL queries, since the former are more "object oriented". In my personal experience, though, the code using criteria is harder to maintain and to check for correctness than HQL/JPQL queries. – John Donn Mar 04 '16 at 09:31

HQL query takes to long to execute

1 Answers1