Mapping file causing unnecessary db queries kills performanc

chrisbloe · **Joined:** Mon Mar 16, 2009 6:12 am **Posts:** 1

I have a problem regarding performance.

The following is my hbm mapping file:

Code:

<hibernate-mapping>
    <class name="...ContactImpl" table="ADDRESSBOOK_CONTACT" lazy="false" select-before-update="true">
        <comment>A Contact within an Addressbook</comment>
        <id name="id" column="ID" unsaved-value="0"><generator class="...HiLoUIDGenerator"/></id>
        <property name="addressbookId" column="ADDRESSBOOK_ID" type="integer" />
        <property name="firstName" column="FIRSTNAME" type="string"/>
        <property name="lastName" column="LASTNAME" type="string"/>

        <set name="childContacts" table="ADDRESSBOOK_CONTACT_RELATIONSHIP" lazy="false" cascade="save-update" inverse="true" >
            <key column="PARENT_CONTACT"/>
            <many-to-many column="CHILD_CONTACT" class="...ContactImpl"/>
        </set>

        <set name="parentContacts" table="ADDRESSBOOK_CONTACT_RELATIONSHIP" lazy="false" cascade="save-update"  >
            <key column="CHILD_CONTACT"/>
            <many-to-many column="PARENT_CONTACT" class="...ContactImpl"/>
        </set>
    </class>
</hibernate-mapping>

The ADDRESSBOOK_CONTACT_RELATIONSHIP table has 2 columns, mapping the parent/child relationship.

It is not unusual to find in excess of 10k contacts to be loaded at the same time. The problem is, when each contact is loaded, each checks for parent AND child contacts. This causes 20k separate queries to be made to the db, causing it to be really slow.

I don't really want to use lazy loading on the sets. Is there a way of minimising the number of unnecessary queries of contacts that have no parents/children?

Thanks in advance.

Hibernate version: 3.1

mmerder · **Posted:** Mon Mar 16, 2009 6:42 am

You could use join fetching so that only one statement using joins is queried.

If you get an exception "cannot fetch multiple bags" make sure you use indexed collections.