[jboss-user] [EJB3] - Hibernate HQL optimization

[csab79]

csab79 [http://community.jboss.org/people/csab79] created the discussion

"Hibernate HQL optimization"

To view the discussion, visit: http://community.jboss.org/message/585768#585768

--------------------------------------------------------------
hello Hibernate gurus! 


I would like to improve my app performance through HQL optimzation. 

I have a small example to show my problem: 


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
" http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="hibernate.classes.Peoples" table="PEOPLES" catalog="Dev1">

            <id name="PId" type="int">
                <column name="P_ID" />
                <generator class="identity"/>
            </id>

            <property name="pfirstname" type="string">
                <column name="p_firstname" />
            </property>
            <property name="plastname" type="string">
                <column name="p_lastname" />
            </property>
            <property name="phobby" type="string">
                <column name="p_hobby" />
            </property>


          <many-to-one name="address" class="hibernate.classes.Address" fetch="join" insert="false" update="false">
                      <column name="p_adress" not-null="true"/>
                  </many-to-one>
</class>




<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
" http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="hibernate.classes.Address" table="ADDRESS" catalog="Dev1">

            <id name="AId" type="int">
                <column name="A_ID" />
                <generator class="identity"/>
            </id>

            <property name="street" type="string">
                <column name="a_street" />
            </property>
            <property name="house" type="string">
                <column name="a_house" />
            </property>
            <property name="district" type="string">
                <column name="a_district" />
            </property>

</class>



I'd like to display rows from people and also show the appropriate person's address (foreign key bind) 
When i use HQL join like this way: 
session.createQuery("from  Peoples inner join Address") 

I suppose in this case .tolist() does not work properly becouse there are additional join properties. 

When i use simple ("from Peopes") HQL query together with many-to-one default lazy="proxy" the result 
will be terrible slow. 
Hibernate try to gets seperatly every row from Address with foreign key selects. (May is it the N+1 Select problem?) 
This is an impact when i have many rows in my table. 

What is the best practice when i always want to display this cross joined datas?  

(pfirstname, plastname, phobby, street, house)


Should i use iterator and manually place each property from the query result? 


Thanks in advance! 
Sorry for the dummy question! :S  
--------------------------------------------------------------

Reply to this message by going to Community
[http://community.jboss.org/message/585768#585768]

Start a new discussion in EJB3 at Community
[http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2029]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20110206/886336be/attachment-0001.html