Relationship is lazy, but an unnecessary SELECT stmt is exec

msenin · **Posted:** Wed Jul 27, 2005 4:10 pm

Hello.

VendorKeyword can have 0 or 1 custom Creative, hense one-to-one relationship in .xml.hbm. The Java code will check if a VendorKeyword object with given primary key exists (session.get(...)) and creates new instance if existing object is not found. Then, if necessary, a new instance of Creative is created and vendorKeyword.setCreative (customCreative).

Here's the problem. Even though the relationship is marked as lazy, Hibernate still issues select statement against the Creative table. What it should, or what I expect it to do, is create a proxy for Creative using customCreativeId from VendorKeyword table. If I later on call vendorKeyword.getCreative(), only then I want the select statement to happen.

The Java code does call vendorKeyword.setCreative(newCreativeInstance) though. I wonder if hibernate tries to detect the change by comparing old value of Creative (null) with the new value, thus executing unnecessary (for me) select statement.

Please help me avoid it. Snippet of Java code and SQL are provided below.

Hibernate version: 3.0.5

Mapping documents:

VendorKeyword.xml.hbm

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-cascade="save-update" default-lazy="true">
    <class name="com.business.sem.VendorKeyword" table="VendorKeyword" lazy="true" dynamic-insert="true" dynamic-update="true">
        <id name="id" type="java.lang.Long" column="id">
          <meta attribute="use-in-tostring">true</meta>
           <generator class="identity" />
        </id>
        <property name="negative" column="isNegative" length="1" type="java.lang.Boolean" not-null="true" />
        <property name="customBid" column="customBid" type="java.math.BigDecimal" not-null="false" />
        <property name="vendorIdString" column="vendorIdString" length="50" type="java.lang.String" not-null="false" />
        <property name="lastUpdate" column="lastUpdate" type="java.sql.Timestamp" not-null="false" update="false" insert="false" />

        <many-to-one class="com.business.sem.Keyword" name="keyword" column="keywordId" not-null="true">
           <meta attribute="field-description">Actual keyword</meta>
        </many-to-one>

      <one-to-one name="customCreative" class="com.business.sem.Creative" foreign-key="customCreativeId" outer-join="true">
         <meta attribute="field-description">Custom creative may be provided for each Keyword</meta>
      </one-to-one>

    </class>
</hibernate-mapping>

Creative.xml.hbm

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-cascade="save-update" default-lazy="true">
    <class name="com.business.sem.Creative" table="Creative" lazy="true" dynamic-insert="true" dynamic-update="true">
        <id name="id" type="java.lang.Long" column="id">
          <meta attribute="use-in-tostring">true</meta>
           <generator class="identity" />
        </id>
        <property name="title" column="title" length="50" type="java.lang.String" not-null="true" />
        <property name="description1" column="description1" length="100" type="java.lang.String" not-null="true" />
        <property name="displayedURL" column="displayedURL" length="256" type="java.lang.String" not-null="false" />

      <!-- Creative does not have to belong to AdGroup - it can be a VendorKeyword.customCreative!!! -->
        <many-to-one name="adGroup" class="com.business.sem.AdGroup" not-null="false">
           <column name="adGroupId" />
        </many-to-one>
    </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

for (int i=0; i<keywords.length; i++) {
            Keyword k = keywords [i];
            com.business.sem.VendorKeyword vk;
            com.business.sem.Keyword localKeyword = getKeyword (k.getText());
            if (localKeyword == null) {
            //    Engine.debug ("CREATED:" + '\"'+k.getText()+'\"');
                localKeyword = newKeywordInstance();
                localKeyword.setSearchString (k.getText());
                localKeyword.setStatus (getStatus (IStatus.ACTIVE_STATUS));
                localKeyword.setSyncStatus (getSyncStatus (ISyncStatus.UPDATED_BY_VENDOR));
            } else {
            //    Engine.debug ("LOADED: " + '\"'+k.getText()+'\"');
            }
            if (ignoreExisting) {
                vk = newVendorKeywordInstance();
                localAdGroup.addToKeywords(vk);
            } else {
                vk = fetchVendorKeyword (localAdGroup, localKeyword); // creates new instance if necessary
            }

            if (k.getMaxCpc() > 0)
                vk.setCustomBid (new BigDecimal (k.getMaxCpc()).divide(new BigDecimal (1000000), 2, BigDecimal.ROUND_HALF_UP));
            vk.setKeyword (localKeyword);
            vk.setMatchType (getCompatibleMatchType (k.getType()));
            vk.setNegative (k.isNegative() ? Boolean.TRUE : Boolean.FALSE);
            vk.setVendorIdString (String.valueOf (k.getId()));
            vk.setStatus(getStatus (IStatus.ACTIVE_STATUS)); //we've just loaded active keywords only
            vk.setSyncStatus (getSyncStatus (ISyncStatus.UPDATED_BY_VENDOR));
            
            // ===================================
            if (k.getDestinationUrl() != null) {
                com.business.sem.Creative customCreative = newCreativeInstance();
                customCreative.setActualURL (k.getDestinationUrl());
                customCreative.setTitle (""); // default title
                customCreative.setDescription1 (""); // default description1
                customCreative.setStatus (getStatus (IStatus.ACTIVE_STATUS));
                customCreative.setSyncStatus (getSyncStatus (ISyncStatus.UPDATED_BY_VENDOR));
                vk.setCustomCreative (customCreative);
            }
            // ===================================
        }

Later in the application:

Code:

    session.saveOrUpdate (account); // this is gonna cascade down the entire object graph.
    session.flush();
    tx.commit();
    session.close();

Full stack trace of any exception that occurs:
None

Name and version of the database you are using:
SQL Server 2000

The generated SQL (show_sql=true):

Code:

4016248 [main] DEBUG org.hibernate.cache.NonstrictReadWriteCache  - Caching: com.business.sem.Keyword#13489
Hibernate: select vendorkeyw0_.id as id, vendorkeyw0_.isNegative as isNegative5_, vendorkeyw0_.customBid as customBid5_, vendorkeyw0_.vendorIdString as vendorId4_5_, vendorkeyw0_.lastUpdate as lastUpdate5_, vendorkeyw0_.status as status5_, vendorkeyw0_.syncStatus as syncStatus5_, vendorkeyw0_.matchType as matchType5_, vendorkeyw0_.keywordId as keywordId5_, vendorkeyw0_.adGroupId as adGroupId5_ from VendorKeyword vendorkeyw0_ where vendorkeyw0_.adGroupId=? and vendorkeyw0_.keywordId=?
[b]Hibernate: select creative0_.id as id2_, creative0_.title as title4_2_, creative0_.description1 as descript3_4_2_, creative0_.description2 as descript4_4_2_, creative0_.description3 as descript5_4_2_, creative0_.displayedURL as displaye6_4_2_, creative0_.actualURL as actualURL4_2_, creative0_.vendorIdString as vendorId8_4_2_, creative0_.lastUpdate as lastUpdate4_2_, creative0_.adGroupId as adGroupId4_2_, creative0_.status as status4_2_, creative0_.syncStatus as syncStatus4_2_, status1_.code as code0_, status1_.name as name7_0_, syncstatus2_.code as code1_, syncstatus2_.description as descript2_8_1_ from Creative creative0_ inner join Status status1_ on creative0_.status=status1_.code inner join SyncStatus syncstatus2_ on creative0_.syncStatus=syncstatus2_.code where creative0_.id=?[/b]
Hibernate: select keyword0_.id as id, keyword0_.searchString as searchSt2_10_, keyword0_.lastUpdate as lastUpdate10_, keyword0_.status as status10_, keyword0_.syncStatus as syncStatus10_ from Keyword keyword0_ where keyword0_.searchString=?

Debug level Hibernate log excerpt:

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

Is there any code in the no-arg constructor of VendorKeyword that sets the Creative relationship? If so, try taking that out and see if that helps.

gavin · **Posted:** Wed Jul 27, 2005 5:13 pm

http://hibernate.org/117.html#A16

msenin · **Posted:** Wed Jul 27, 2005 10:40 pm

Quote:

Lazy fetching is only conceptually possible for a mandatory association since we have to hit the other table to determine whether the association is null or not!

Not necessarily. If I provide the column="xxx", i.e. if master table has the foreign key, you don't need to do select - because you'd know from foreign key if it's null or not.

I just came up with a workaround before I read the responses to the thread. I define relationship as <many-to-one unique="true" lazy="true">, which makes it a many-to-one relationship of granularity 1, i.e. an equivalent of one-to-one. It seems hibernate is completely ignoring lazy part (in Hibernate 3, all relationships are lazy by default) for the one-to-one relationship and trying to fetch the dependent object anyway. But now it doesn't. Not the neetest solution though...