What Hibernate Likes to Do With Your Lovely SQL

Discussions

Performance and scalability: What Hibernate Likes to Do With Your Lovely SQL

  1. What Hibernate Likes to Do With Your Lovely SQL (1 messages)

    Hibernate sometimes doesn’t like you at all. You, the seasoned JDBC/Oracle developer. Some of you have learned that to query using HQL, you must use the hibernate equivalent object names, but still you persist (forgive me the pun, there,) in writing HQL that looks like SQL!! This is bad. This is bad because your application is suffering. It’s suffering from verbosity, and from poor performance. Here is why - and here’s what to do about it. Let’s take the following example HQL query: select o from ManyToOneObject1 mo1, ManyToOneObject2 mo2, ObjectWeReallyWant o where mo2.number = ? and o.manyToOneObject2 = mo2 and o.manyToOneObject1 = mo1 and mo1.idOfSomeKind in (select distinct mo3.idOfSomeKind from [some subquery to do with filtering records]) order by mo1.descriptionField This is an actual query from “the field.” The first thing that struck me as odd about the query was that it forcibly joined many to one objects instead of just asking for the object they wanted. Like this: from ObjectWeReallyWant as o where o.manyToOneObject1.number = ? And o.manyToOneObject1.idFieldOfSomeKind in (select idFieldOfSomeKind from [subquery stuff here]) Order by o.manyToOneObject1.descriptionField That’s a little easier to read. Either way we format the query, there is a big problem! When I looked at the SQL output in the logs of our application, I found one main query, followed by a very long list of select statements - one for each many to one record! But why? We can specify a “join” or a “select” for those mappings, and each setting will cause a different problem. Join - we can have a cartesian product pulled back from Oracle. Hibernate eliminates the duplicates… but isn’t that what an INNER join is for? Select - Hibernate will issue a separate select statement for each record. Since neither of these options is what we want (an Oracle developer would look at our original HQL and think “inner join,”) we can tell Hibernate exactly how to fetch those relationships, as follows: from ObjectWeReallyWant as o inner join fetch o.manyToOneObject1 inner join fetch o.manyToOneObject2 where o.manyToOneObject1.number = ? And o.manyToOneObject1.idFieldOfSomeKind in (select idFieldOfSomeKind from [subquery stuff here]) Order by o.manyToOneObject1.descriptionField Using the “inner join fetch” HQL statement does what we expected the original statement to do - inner join. In our application this resulted in one statement, and no extra records. To find this and other problems (Hibernate LOVES to left outer join, leading to cartesian product hell,) turn on your SQL output. (Use property: “hibernate.show_sql=true”)

    Threaded Messages (1)

  2. Thanks[ Go to top ]

    Thanks I will broad this writing my own site www.kepaze.org