-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: HQL with multiple join?
PostPosted: Mon May 07, 2007 6:08 am 
Newbie

Joined: Thu Apr 26, 2007 11:25 pm
Posts: 5
can i have something like this?

A, B & C is tables that i wanted to join.
column a1, & a2 belongs to table A.
column b1 belongs to table B.
column c1, c2, c3 belongs to table C.

Code:
list = session.createCriteria(A.class)
.add(Restrictions.eq("a1", no))
.add(Restrictions.eq("a2", name))
.setFetchMode("B",FetchMode.JOIN)
.add(Restrictions.eq("b1", venId))
.setFetchMode("C",FetchMode.JOIN)
.add(Restrictions.eq("c1", new Boolean(false)))
add(Restrictions.eq("c2", IXConstant.STATUS_ACTIVE))
.add(Restrictions.eq("c3", spCatSelected))
.list();

The mapping

<class name="A" table="A">
<id name="partId" column="partId" type="long">
<generator class="identity"/>
</id>
      
<property name="a1" type="string">
<column name="a1" length="10" not-null="true" unique="true"/>
</property>
<property name="a2" type="string">
<column name="a2" length="2000" not-null="true"/>
</property>

<many-to-one name="C" class="C" column="categoryId"/>
      
<set name="B"cascade="all" inverse="true">
<key column="partId"/>
<one-to-many not-found="ignore" class="B"/>
</set>
</class>


The performance is still quite slow, how can i further enhance the query in order to have a better performance?


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 07, 2007 8:36 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
Hmm...what does the generated SQL look like?


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 07, 2007 1:42 pm 
Newbie

Joined: Thu Jan 04, 2007 1:36 pm
Posts: 16
Few questions to answer before you start fine-tuning the query:
1. Do you need ALL the columns from A,B and C? Probably not. If thats the case, then you could use Projections and turn it into a reporting query, instead of loading all the data into memory by joining 3 tables.
2. Do you need an INNER JOIN or a OUTER JOIN. Based on your Criteria, you're using an INNER join.. but need to make sure that thats what you want.
3. Finally, you can check to see if you have indexes on the columns that the data is being filtered by (especially if the 3 tables have tons of data).

Also, if you can use show_sql in your hibernate configuration file and post the generated SQL, that might help.
- Arun


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.