Hibernate Community • View topic - Newbie-ish Inefficient Join / HQL Question

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Newbie-ish Inefficient Join / HQL Question

Page 1 of 1

[ 12 posts ]

Previous topic | Next topic

Author

Message

mephesto

Post subject: Newbie-ish Inefficient Join / HQL Question

Posted: Fri Jan 23, 2009 8:13 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

Hey,

I have got the basics of HQL down, but now I'm getting into more complex queries as I port my application over and am wondering what the best way to handle this is.

I have these two POJO's and their definitions:

Code:

   <class name="ovm.central.Title" table="title">
      <id name="id" column="title_pk">
         <generator class="sequence">
            <param name="sequence">title_seq</param>
         </generator>
      </id>
      <many-to-one name="assetBillingState" column="asset_billing_state_fk" class="ovm.central.AssetBillingState"/>
      <property name="adult" column="adult"/>
      <property name="length" column="length"/>
      <property name="boxOffice" column="box_office"/>
      <property name="status" column="status"/>
      <property name="director" column="director"/>
      <property name="producer" column="producer"/>
      <property name="actors" column="actors"/>
      <property name="macrovision" column="macrovision"/>
      <set name="titleLanguageDataInternal" table="title_lang_data">
         <key column="title_fk"/>
         <one-to-many class="ovm.central.TitleLanguageData"/>
      </set>
   </class>


   <class name="ovm.central.TitleLanguageData" table="title_lang_data">
      <id name="id" column="title_lang_data_pk">
         <generator class="sequence">
            <param name="sequence">title_lang_data_seq</param>
         </generator>
      </id>
      <many-to-one name="title" column="title_fk" class="ovm.central.Title"/>
      <many-to-one name="language" column="language_fk" class="ovm.central.Language"/>
      <property name="titleString" column="title"/>
      <property name="synopsisString" column="synopsis"/>
   </class>

I am trying to query all titles, but I want to only show the english title string. I have this working HQL:

Code:

return sessionFactory.getCurrentSession().createQuery("SELECT title FROM Title title JOIN title.titleLanguageDataInternal tld WHERE title.id = tld.title.id AND tld.language.abbr = :abbr ORDER BY tld.title")
.setString("abbr", "en")
.list();

However, when it runs, it runs 1400 queries, basically subqueries for each title returned which it shouldn't have to. It should be a standard inner join, what am I missing?

The subquery generated looks like this:

Code:

Hibernate: select titlelangu0_.title_fk as title2_2_, titlelangu0_.title_lang_data_pk as title1_2_, titlelangu0_.title_lang_data_pk as title1_994_1_, titlelangu0_.title_fk as title2_994_1_, titlelangu0_.language_fk as language3_994_1_, titlelangu0_.title as title994_1_, titlelangu0_.synopsis as synopsis994_1_, language1_.language_pk as language1_991_0_, language1_.language as language991_0_, language1_.abbr as abbr991_0_ from title_lang_data titlelangu0_ left outer join languages language1_ on titlelangu0_.language_fk=language1_.language_pk where titlelangu0_.title_fk=?

Thanks in advance.

Top

pkleindl

Post subject:

Posted: Sat Jan 24, 2009 6:28 am

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

The subqueries are needed because you only load the title objects with your query, the WHERE clause only reduces the resultset but its values are not loaded and instantiated.

If you want a List of Title objects and initialize TitleLanguageData at the same time, use JOIN FETCH.
If you want a List of String objects use "SELECT tld.titleString FROM ...". Will return a List<Object[]>, you can cast the [0] object to String.
You don't need the "title.id = tld.title.id" part in your query, hibernate should do that automatically when you write title.titleLanguageDataInternal.

Hope that helps.

Top

mephesto

Post subject:

Posted: Mon Jan 26, 2009 3:37 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

Thanks for the info but I'm still a little confused. I have been reviewing the join fetch usages but haven't found one that accurately demonstrates my situation.

To clarify, I am trying to join on my one to many relationship, but restrict the result so it's essentially one-to-one.

ie: A single title object can have multiple translations of it's title and synopsis data in languages english, spanish but instead of returning the title with english and spanish language data, I only want the english data returned from the data store.

Any attempt to reference the titleLangaugeData in HQL like this:

Code:

return sessionFactory.getCurrentSession().createQuery("FROM Title t JOIN FETCH t.assetBillingState JOIN FETCH t.titleLanguageDataInternal ORDER BY t.titleLanguageDataInternal.title").list();

returns an error like this:

Code:

org.hibernate.QueryException: illegal attempt to dereference collection [title0_.title_pk.titleLanguageDataInternal] with element property reference [title] [FROM ovm.central.Title t JOIN FETCH t.titleLanguageDataInternal ORDER BY t.titleLanguageDataInternal.title] at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:46) at...

But all I'm really wanting to do would be the equivalent of this in SQL:

Code:

SELECT * FROM title t, title_lang_data tld WHERE t.title_pk = tld.title_fk AND tld.language_fk = 1

If I need to use the JSP to show the language I want that's fine but how will I be able to use HQL to order by the english title field?

Thanks.

Top

pkleindl

Post subject:

Posted: Mon Jan 26, 2009 5:22 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

According to http://opensource.atlassian.com/project ... e/HHH-2667 this kind of query was never intended to work that way and now results in this error message.

As your titleLanguageDataInternal has a to-one relationship to title it should work as

Code:

select tld.title from titleLanguageDataInternal tld join fetch tld.title where tld.language.abbr = :abbr order by tld.title

(or something very close to that at least)

good luck and rating is appreciated if that works out

Top

mephesto

Post subject:

Posted: Mon Jan 26, 2009 6:23 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

Thanks for the prompt reply pkleindl,

I'm understanding where your going but it's still not quite working. This query:

SELECT tld.title FROM TitleLanguageData tld JOIN FETCH tld.title WHERE tld.language.abbr = :abbr ORDER BY tld.title

Results in:

org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=title,tableAlias=title1_,origin=title_lang_data titlelangu0_,colums={titlelangu0_.title_fk ,className=ovm.central.Title}}] [SELECT tld.title FROM ovm.central.TitleLanguageData tld JOIN FETCH tld.title WHERE tld.language.abbr = :abbr ORDER BY tld.title] at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:195) at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)

.. If I remove the FETCH clause, it will return the data, but still doing the 1400 individual queries.

.. If I remove the SELECT tld.title and use JOIN FETCH it runs 1400 queries and returns a titleLanguageDataList object.

Any further thoughts? Thanks!

Top

pkleindl

Post subject:

Posted: Mon Jan 26, 2009 7:29 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

Quote:

.. If I remove the SELECT tld.title and use JOIN FETCH it runs 1400 queries and returns a titleLanguageDataList object.

That shouldn't happen.

With "SELECT tld" (not tld.title) you should get a List<TitleLanguageData> and all associated Title objects should get initialized too.

Can you post the code where you do the query and the loop?

Top

mephesto

Post subject:

Posted: Mon Jan 26, 2009 7:53 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

Here's the query as you request:

SELECT tld FROM TitleLanguageData tld JOIN FETCH tld.title WHERE tld.language.abbr = :abbr ORDER BY tld.titleString

and the resulting query that is looped 1400 times is:

Hibernate: select titlelangu0_.title_fk as title2_2_, titlelangu0_.title_lang_data_pk as title1_2_, titlelangu0_.title_lang_data_pk as title1_3963_1_, titlelangu0_.title_fk as title2_3963_1_, titlelangu0_.language_fk as language3_3963_1_, titlelangu0_.title as title3963_1_, titlelangu0_.synopsis as synopsis3963_1_, language1_.language_pk as language1_3961_0_, language1_.language as language3961_0_, language1_.abbr as abbr3961_0_ from title_lang_data titlelangu0_ left outer join languages language1_ on titlelangu0_.language_fk=language1_.language_pk where titlelangu0_.title_fk=?

Am I correct in assuming the issue actually lies in the language association?

Here's that mapping:

Code:

<class name="ovm.central.Language" table="languages">
   <id name="id" column="language_pk">
      <generator class="sequence">
         <param name="sequence">languages_seq</param>
      </generator>
   </id>
   <property name="name" column="language"/>
   <property name="abbr" column="abbr"/>
</class>

The spring object registered to the ModelMap view is: titleLanguageDataList: [1, 2, 3...] but I'm not concerned about that, more about the subqueries.

I'll continue investigating this as well. Thanks again.

EDIT:

I've been able to get it fixed so there's no redundant subqueries... however it doesn't seem right. This syntax doesn't seem right for my HQL:

return sessionFactory.getCurrentSession().createQuery("FROM TitleLanguageData tld JOIN FETCH tld.title JOIN FETCH tld.title.titleLanguageDataInternal WHERE tld.language.abbr = :abbr ORDER BY tld.titleString")
.setString("abbr", "en")
.list();

Why do I need to explicitly specifify the JOIN to the parent and then again when the parent JOIN's back to the child? Hibernate should be smarter than that, no?

Top

pkleindl

Post subject:

Posted: Tue Jan 27, 2009 1:28 am

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

Maybe someone with more experience than me can answer that but i think this shouldn't be necessary.

I suspect that the reason might be in how you access the data from your frontend, if you have a value field bound to a get-method that directly accesses the database this might be the result.

This might require a whole lot more of code-digging...

Top

mephesto

Post subject:

Posted: Tue Jan 27, 2009 2:59 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

I can't see why the front-end would have anything to do with it. I'm using Spring 2 w/ Hibernate 3, just like the infamous 'petclinic' example implements it in the Spring sample apps.

I've found tons of examples of one-to-many relationships where you query from the parent to the child, using parent members in the where clause, but never any instance where you use a child member in the where clause.

Hopefully someone else can comment on this, but it is working so I'm going to move on for now anyhow. Thanks alot, I spotted you a couple points for helping me understand the JOIN FETCH clause.

Top

pkleindl

Post subject:

Posted: Tue Jan 27, 2009 4:01 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

Thanks a lot for the rating :o)

One last shot at your statement:

Code:

SELECT DISTINCT t FROM Title t left join fetch t.titleLanguageDataInternal tld left join fetch tld.language lang
WHERE lang.abbr = :abbr

If this doesn't work i'm going to take it personally an code it myself with your current infos :o>>

Top

mephesto

Post subject:

Posted: Tue Jan 27, 2009 4:44 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

Nice one, that also works and is less convoluted. I never thought to use the distinct keyword in that sense but it does work as I'd expect.

Thanks a lot, you really earned that last point ;)

Cheers,

Lindsey

Top

pkleindl

Post subject:

Posted: Tue Jan 27, 2009 5:05 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

Cheers too, and not only because i'm a "Beginner" now ;o))

I had a similar case recently and just got the order mixed up before... the distinct is needed because with the JOIN FETCH several answer rows are possible which need to be reduced.

Another possibility would be to cast the List to a Set, this removes the double entries too.

See ya'round

Top

Page 1 of 1

[ 12 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum