Results 1 to 18 of 18
Like Tree1Likes
  • 1 Post By Tolls

Thread: hql vs criteria - what is the performance gain?

  1. #1
    Cbani is offline Member
    Join Date
    Jan 2010
    Posts
    95
    Rep Power
    0

    Default hql vs criteria - what is the performance gain?

    I had the below query in Criteria which was taking 2 mins to to execute (return from the method)


    public final Data getData(final Long dataid) {
    Criteria dataCriteria = session.createCriteria(Data.class)
    .setFetchMode("mgr")
    .setFetchMode("tmgr")
    .setFetchMode("comp")
    .createAlias("myent","myent", Criteria.LEFT_JOIN)
    .createAlias("myent.party", "pardata", Criteria.LEFT_JOIN)
    .createAlias("slabs","slabs", Criteria.LEFT_JOIN)
    .createAlias("tpr", "tpr", Criteria.LEFT_JOIN)
    .createAlias("tpr.par", "par",Criteria.LEFT_JOIN)
    .createAlias("par.LE_EN", "le",Criteria.LEFT_JOIN)
    .createAlias("le.RAT", "ler",Criteria.LEFT_JOIN)
    .createAlias("c","c",Criteria.LEFT_JOIN)
    .createAlias("c.crd","crd",Criteria.LEFT_JOIN)
    .createAlias("crd.coc", "coc",Criteria.LEFT_JOIN)
    .createAlias("coc.thpr","cocthpr",Criteria.LEFT_JO IN)
    .createAlias("cocthpr.par", "cocpar",Criteria.LEFT_JOIN)
    .add(Restrictions.eq("dataid", dataid));
    return (Data) dataCriteria.uniqueResult();

    }


    I refactored this to an HQL and now it takes 10 seconds to return from the method/execute the query. Below is the HQL version.

    public final Data getData(final Long dataid) {
    StringBuffer qbuffer = new StringBuffer();
    qbuffer.append("select data from com.a.b.c.Data as data");
    qbuffer.append(" left join fetch data.mgr mgr");
    qbuffer.append(" left join fetch data.tmgr tmgr");
    qbuffer.append(" left join fetch data.comp comp");
    qbuffer.append(" left join fetch data.myent myent");
    qbuffer.append(" left join fetch myent.par pardata");
    qbuffer.append(" left join fetch data.slabs slabs");
    qbuffer.append(" left join fetch data.tpr tpr");
    qbuffer.append(" left join fetch tpr.par par");
    qbuffer.append(" left join fetch par.LE_EN le");
    qbuffer.append(" left join fetch le.RAT ler");
    qbuffer.append(" left join fetch data.cd c");
    qbuffer.append(" left join fetch data.crd crd");
    qbuffer.append(" left join fetch crd.coc coc");
    qbuffer.append(" left join fetch coc.thpr cocthpr");
    qbuffer.append(" left join fetch cocthpr.par cocpar");
    qbuffer.append(" where data.dataid = :dataid ");

    Query query = getSession().createQuery(qbuffer.toString()).setLo ng("dataid", dataid);

    return (Data)query.uniqueResult();
    }

    Can somebody please explain? If needed I will provide the HBM mappings (Lazy/eager etc.). Note->There was no other modification done on anything other than the converion from Criteria to HQL.

  2. #2
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: hql vs criteria - what is the performance gain?

    ... I don't know your schema, but the queries look different? The criteria has different joins than the manual hql.

    In any case, turn on SQL logging in Hibernate or use something like log4jdbc to see what native queries are being generated. To solve performance issues in an ORM you need to start from the bottom. I'm pretty sure whatever problem there is exists at the SQL level. If you have an SQL at least you can ask the DBMS for an explain plan.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  3. #3
    Cbani is offline Member
    Join Date
    Jan 2010
    Posts
    95
    Rep Power
    0

    Default Re: hql vs criteria - what is the performance gain?

    <quote>... I don't know your schema, but the queries look different? The criteria has different joins than the manual hql."</quote>

    Thanks gimbal2, can you please explain where do you see the difference in both the queries.. in both the cases either of the queries are using left join only, right?
    Last edited by Cbani; 05-19-2015 at 02:11 AM.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    What version of Hibernate are you using?

    Have you logged the query generated?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: hql vs criteria - what is the performance gain?

    Quote Originally Posted by Cbani View Post
    <quote>... I don't know your schema, but the queries look different? The criteria has different joins than the manual hql."</quote>

    Thanks gimbal2, can you please explain where do you see the difference in both the queries.. in both the cases either of the queries are using left join only, right?
    You're ignoring the other half of my post.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  6. #6
    Cbani is offline Member
    Join Date
    Jan 2010
    Posts
    95
    Rep Power
    0

    Default Re: hql vs criteria - what is the performance gain?

    Quote Originally Posted by Tolls View Post
    What version of Hibernate are you using?

    Have you logged the query generated?
    It's 3.6.10.Final
    I did log the queryies(using HQL and using criteria separately), but the generated queries are quite different. I am analyzing them.

  7. #7
    Cbani is offline Member
    Join Date
    Jan 2010
    Posts
    95
    Rep Power
    0

    Default Re: hql vs criteria - what is the performance gain?

    Quote Originally Posted by gimbal2 View Post
    I'm pretty sure whatever problem there is exists at the SQL level. If you have an SQL at least you can ask the DBMS for an explain plan.
    If it was at the SQL level then the generated SQL by the criteria should also take appoximately the same amount of time. But in my case the generated query is quite fast if executed through SQL developer.

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    Quote Originally Posted by Cbani View Post
    It's 3.6.10.Final
    Then the code above is wrong.
    Criteria does not have a setFlushMode that takes only one parameter.

    Quote Originally Posted by Cbani View Post
    I did log the queryies(using HQL and using criteria separately), but the generated queries are quite different. I am analyzing them.
    Then those two versions above are quite clearly not the same.
    As gimbal says, it's the SQL at the end of the day that's important.
    So what is the difference between the HQL and the Criteria versions?
    Is one making more round trips fetching data than the other? After all, you are asking for everything in one go, so I doubt it's going to be a single query.
    gimbal2 likes this.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    As gimbal has pointed out to me that should have said setFetchMode, not setFlushMode...but the point still stands that it has two parameters, and you've only provided one.
    :)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  10. #10
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: hql vs criteria - what is the performance gain?

    Did you know you can edit posts and provide an edit reason? ;)
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    I wanted to highlight my lack of proof-reading abilities...or something like that anyway.
    :)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  12. #12
    Cbani is offline Member
    Join Date
    Jan 2010
    Posts
    95
    Rep Power
    0

    Default Re: hql vs criteria - what is the performance gain?

    Quote Originally Posted by Tolls View Post
    Then the code above is wrong.
    Criteria does not have a setFlushMode that takes only one parameter.
    You are right, That was a mistake from myside while putting the code in the forum as I did modify the naming conventions etc.. before putting it. Thanks for highlighting it.

    The code is as below -

    Java Code:
    public final Data getData(final Long dataid) {
    Criteria dataCriteria = session.createCriteria(Data.class)
    .setFetchMode("mgr", FetchMode.JOIN)
    .setFetchMode("tmgr", FetchMode.JOIN)
    .setFetchMode("comp", FetchMode.JOIN)
    .createAlias("myent","myent", Criteria.LEFT_JOIN)
    .createAlias("myent.party", "pardata", Criteria.LEFT_JOIN)
    .createAlias("slabs","slabs", Criteria.LEFT_JOIN)
    .createAlias("tpr", "tpr", Criteria.LEFT_JOIN)
    .createAlias("tpr.par", "par",Criteria.LEFT_JOIN)
    .createAlias("par.LE_EN", "le",Criteria.LEFT_JOIN)
    .createAlias("le.RAT", "ler",Criteria.LEFT_JOIN)
    .createAlias("c","c",Criteria.LEFT_JOIN)
    .createAlias("c.crd","crd",Criteria.LEFT_JOIN)
    .createAlias("crd.coc", "coc",Criteria.LEFT_JOIN)
    .createAlias("coc.thpr","cocthpr",Criteria.LEFT_JO IN)
    .createAlias("cocthpr.par", "cocpar",Criteria.LEFT_JOIN)
    .add(Restrictions.eq("dataid", dataid));
    return (Data) dataCriteria.uniqueResult();
    
    }
    I refactored this to an HQL and now it takes 10 seconds to return from the method/execute the query. Below is the HQL version.

    Java Code:
    public final Data getData(final Long dataid) { 
    StringBuffer qbuffer = new StringBuffer();
    qbuffer.append("select data from com.a.b.c.Data as data");
    qbuffer.append(" left join fetch data.mgr mgr");
    qbuffer.append(" left join fetch data.tmgr tmgr");
    qbuffer.append(" left join fetch data.comp comp");
    qbuffer.append(" left join fetch data.myent myent");
    qbuffer.append(" left join fetch myent.par pardata");
    qbuffer.append(" left join fetch data.slabs slabs");
    qbuffer.append(" left join fetch data.tpr tpr");
    qbuffer.append(" left join fetch tpr.par par");
    qbuffer.append(" left join fetch par.LE_EN le");
    qbuffer.append(" left join fetch le.RAT ler");
    qbuffer.append(" left join fetch data.cd c");
    qbuffer.append(" left join fetch data.crd crd");
    qbuffer.append(" left join fetch crd.coc coc");
    qbuffer.append(" left join fetch coc.thpr cocthpr");
    qbuffer.append(" left join fetch cocthpr.par cocpar");
    qbuffer.append(" where data.dataid = :dataid ");
    
    Query query = getSession().createQuery(qbuffer.toString()).setLo ng("dataid", dataid);
    
    return (Data)query.uniqueResult();
    }

    Quote Originally Posted by Tolls View Post
    So what is the difference between the HQL and the Criteria versions?
    Is one making more round trips fetching data than the other? After all, you are asking for everything in one go, so I doubt it's going to be a single query.
    It is not a single query in either of the cases. Am I right in the assumption that (also as mentioned by Tolls) HQl and criterias use different strategies while generating the native queries (as the generated queries does not look similar (round trips, etc..) ) ?
    Last edited by Cbani; 05-20-2015 at 03:08 PM.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    They might do, however it's unlikely it should generate significantly different results.

    Do you get the same results back from the two queries?
    Actually, that would be hard to tell...

    It should be obvious if one is returning a more "complete" Data object than the other.
    I would also ask how you are testing the timings?
    Do you time one and then tear down and startup before timing the other?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  14. #14
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: hql vs criteria - what is the performance gain?

    We're talking about SQL here; even the ordering of criteria can make the difference between the database needing to do a full table scan or being able to go through an index.

    There is no cookie-cutter way to pin down and solve these kind of problems, I've seen all shapes and sizes of performance problems - including a single lazy load on a very stupid oneToMany mapping triggering 2 million records to be tanked into memory when the position of the stars was exactly right (my favorite kind of problem: the one that doesn't happen consistently).

    Let me just say this: I *hope* it is an SQL performance problem that boils down to one path leading to for example a left join turning into a regular join.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    The ordering of criteria should make no difference.
    It's been the best part of two decades since that applied to Oracle for example, and similar for SQL Server.
    I've not seen it with MySQL either.

    For a timing difference like that it's either not getting the same data or one is pulling from a cache at least some of the time.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  16. #16
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: hql vs criteria - what is the performance gain?

    Pardon, color me stupid for mixing trivia with contextual facts. I have no idea why I touched upon the least likely culprit first.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  17. #17
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: hql vs criteria - what is the performance gain?

    Well, it's true we don't know what DB is being targetted...
    :)
    For some reason I thought they'd said Oracle.

    I'm clearly merging threads in my brain.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  18. #18
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: hql vs criteria - what is the performance gain?

    Quote Originally Posted by Tolls View Post
    Well, it's true we don't know what DB is being targetted...
    Post #7 mentions the SQL Developer tool, so you were safe to assume Oracle.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

Similar Threads

  1. I want to help (to gain experience)
    By ozzyman in forum Jobs Wanted
    Replies: 3
    Last Post: 12-17-2012, 01:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •