how to force join (overriding MaxFetchDepth=0)

jhudson8 · **Posted:** Wed Jan 11, 2006 1:50 am

Hibernate version: 3.03

Name and version of the database you are using: MySQL 5.0.11

This isn't relating to an error but a configuration question:

Given a database with many foreign key constraints, I’ve set the fetch depth to 0 (<attribute name=”MaxFetchDepth”>0</attribute>). This was done to shorten the length of some extraordinarily long SQL queries (and subsequently the load times) when most of the data was not required for listing pages.

There are a few cases where I would like to include some many-to-one relationships in the join (basically to override the MaxFetchDepth=0). Is there anyway I can do this? I have tried setting the fetch=”join” and lazy=”false” attributes on the many-to-one but that has not worked.

I would really appreciate any advice. Thanks in advance.

Joe

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

Hi,

If you read the docs in 4.4. Optional configu ration properties - hibernate.max_fetch_depth, it says: Set a maximum "depth" for the outer join fetch tree for single-ended associations (one-to-one, manyto-one). A 0 disables default outer join fetching. eg. recommended values between 0 and 3

So, you do need to use max_fetch_depth > 0 to enable non-lazy loading in your app.

Also, by default, Hibernate3 uses lazy select fetching for collections and lazy proxy fetching for single-valued associations. (See 20.1. Fetching strategies section). So, if you don't specify the lazy setting, it should by default not cause long SQL queries to get data from FK related tables.

Also, this statement from the Hibernate docs tries to emphasize an important point: "We have two orthogonal notions here: when is the association fetched, and how is it fetched (what SQL is
used). Don't confuse them! We use fetch to tune performance. We may use lazy to define a contract for what
data is always available in any detached instance of a particular class."

I think what you are looking for is max_fetch_depth some value greater than 0. Also lazy="false" along with fetch="join" with for the cases where you do want to load non-lazy objects.

Hope this helps.
-Mrtz

jhudson8 · **Posted:** Wed Jan 11, 2006 4:28 pm

Thank you so much for your time, Mrtz.

Joe

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

would appreciate a credit in the rating system if it helped.
Thanks,
Mrtz

LaLiLuna · **Posted:** Wed Jan 11, 2006 5:20 pm

if you want to fetch a specifiq relation you can use fetch in a query.
e.g.
select i from Invoice i inner join fetch i.order

Regards Sebastian

jhudson8 · **Posted:** Wed Jan 11, 2006 6:35 pm

So, I've tried what you said Mrtz and am still having trouble. Would you have any idea as to why the join is not working?

I have many mapping files but I'll put in the 2 that I'm working with (PIF and Property). PIF has a many-to-one of Property which I have set as lazy="false" and fetch="join". I have also set MaxFetchDepth="3". The query is still not joining these values

Query I'm Using

Code:

      from PIF pif
      where pif.CurrentOwner.Id = ?
      or pif.Id in (
         select a.PIF.Id
         from Activity a
         where a.AssignedTo.Id = ?
      )
      order by pif.CreatedDate

Configuration File

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE server>

<server>
   <mbean code="org.jboss.hibernate.jmx.Hibernate" name="jboss.har:service=Hibernate">
      <!-- depends>jboss.jca:service=RARDeployer</depends -->
      <depends>jboss.jca:service=DataSourceBinding,name=jdbc/MAFC</depends>

      <attribute name="DatasourceName">java:/jdbc/MAFC</attribute>
      <attribute name="SessionFactoryName">java:/hibernate/MAFCHibernateFactory</attribute>
      <!-- attribute name="CacheProviderClass">org.hibernate.cache.OSCacheProvider</attribute -->

      <attribute name="SecondLevelCacheEnabled">true</attribute>
      <attribute name="CacheProviderClass">com.mafc.moses.cache.OSCacheProvider</attribute>

      <attribute name="Dialect">org.hibernate.dialect.MySQLDialect</attribute>
      <attribute name="QueryCacheEnabled">true</attribute>
      <attribute name="ShowSqlEnabled">true</attribute>
      <attribute name="MaxFetchDepth">3</attribute>
   </mbean>
</server>

Mapping Files (PIF & Property)

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.mafc.moses.bo">
   <class
      name="PIF"
      table="pif"
      dynamic-update="true"
   >
      <cache usage="read-write"/>
      <id
         name="Id"
         type="java.lang.Long"
         column="pif_id"
      >
         <generator class="identity"/>
      </id>

      <property
         name="ReservationNumber"
         column="reservation_nbr"
         type="string"
         not-null="false"
         length="35"
      />
      <property
         name="CreatedDate"
         column="created_date"
         type="timestamp"
         not-null="true"
         length="19"
      />
      <property
         name="ListPrice"
         column="list_price"
         type="big_decimal"
         not-null="false"
         length="12"
      />
      <property
         name="ListDate"
         column="list_date"
         type="date"
         not-null="false"
      />
      <property
         name="AcceptPrice"
         column="accept_price"
         type="big_decimal"
         not-null="false"
         length="12"
      />
      <property
         name="AcquisitionAmount"
         column="acquisition_amount"
         type="big_decimal"
         not-null="false"
         length="12"
      />
      <property
         name="MortgageRemainingBalance"
         column="mortgage_remaining_balance"
         type="big_decimal"
         not-null="false"
         length="12"
      />
      <property
         name="CurrentInPayments"
         column="is_current_in_payments"
         type="true_false"
      />
      <property
         name="RepairRoof"
         column="repair_roof"
         type="true_false"
      />
      <property
         name="RepairInteriorPaint"
         column="repair_interior_paint"
         type="true_false"
      />
      <property
         name="RepairExteriorPaint"
         column="repair_exterior_paint"
         type="true_false"
      />
      <property
         name="RepairCarpet"
         column="repair_carpet"
         type="true_false"
      />
      <property
         name="RepairFurnace"
         column="repair_furnace"
         type="true_false"
      />
      <property
         name="RepairAC"
         column="repair_ac"
         type="true_false"
      />
      <property
         name="RepairWindows"
         column="repair_windows"
         type="true_false"
      />
      <property
         name="RepairCabinets"
         column="repair_cabinets"
         type="true_false"
      />
      <property
         name="RepairStove"
         column="repair_stove"
         type="true_false"
      />
      <property
         name="RepairRefridgerator"
         column="repair_refridgerator"
         type="true_false"
      />
      <property
         name="OtherRepairs"
         column="other_repairs"
         type="string"
         not-null="false"
      />
      <property
         name="OtherImprovements"
         column="other_improvements"
         type="string"
         not-null="false"
      />
      <property
         name="VacateDate"
         column="vacate_date"
         type="date"
         not-null="false"
      />
      <property
         name="OutsaleAmount"
         column="outsale_amount"
         type="big_decimal"
         not-null="false"
      />
      <property
         name="OutsaleDepositAmount"
         column="outsale_deposit_amount"
         type="big_decimal"
         not-null="false"
      />

      <many-to-one
         name="Property"
         column="property_id"
         class="Property"
         not-null="true"
         lazy="false"
         fetch="join"
      >
      </many-to-one>
      <many-to-one
         name="ContactPerson"
         column="contact_person_id"
         class="Person"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="CurrentOwner"
         column="current_owner_id"
         class="User"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="CurrentValuation"
         column="current_valuation_id"
         class="Valuation"
      >
      </many-to-one>
      <many-to-one
         name="RequestedValuation"
         column="requested_valuation_id"
         class="Valuation"
      >
      </many-to-one>
      <many-to-one
         name="CurrentAcquisitionOffer"
         column="current_acquisition_offer_id"
         class="AcquisitionOffer"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="CurrentOutsaleOffer"
         column="current_outsale_offer_id"
         class="OutsaleOffer"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="TermiteInspection"
         column="termite_inspection_id"
         class="ContractorInspection"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="HVACInspection"
         column="hvac_inspection_id"
         class="ContractorInspection"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="MoldInspection"
         column="mold_inspection_id"
         class="ContractorInspection"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="HomeInspection"
         column="home_inspection_id"
         class="Valuation"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="DriveByInspection"
         column="drive_by_inspection_id"
         class="Valuation"
         not-null="false"
      >
      </many-to-one>

      <many-to-one
         name="OccupancyType"
         column="occupancy_type"
         class="OccupancyType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="Condition"
         column="property_condition"
         class="PropertyCondition"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="MLSStatus"
         column="mls_status"
         class="MlsStatus"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="Stage"
         column="property_stage"
         class="PropertyStage"
         not-null="true"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="SubStage"
         column="property_sub_stage"
         class="PropertySubStage"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="LeaseType"
         column="lease_type"
         class="LeaseType"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="SellReason"
         column="sell_reason"
         class="SellReason"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="AdvertisingType"
         column="advertising_type"
         class="AdvertisingType"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="DeadDealReason"
         column="dead_deal_reason"
         class="DeadDealReason"
         lazy="false"
      >
      </many-to-one>

      <bag name="Events" order-by="created_date desc" cascade="save-update">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <one-to-many class="PropertyEvent"/>
      </bag>
      <bag name="UnapprovedJobs" order-by="created_date asc" where="is_approved = 'F' and is_submitted_for_approval = 'F'" inverse="true">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <one-to-many class="Job"/>
      </bag>
      <bag name="AvailableJobs" order-by="created_date asc" where="bid_id is null and is_approved = 'T'" inverse="true">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <one-to-many class="Job"/>
      </bag>
      <set name="Owners" sort="natural" cascade="none" inverse="true" where="type='O'" table="pif_person">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <many-to-many column="person_id" class="Person"/>
      </set>
      <set name="Tenants" sort="natural" cascade="none" inverse="true" where="type='T'" table="pif_person">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <many-to-many column="person_id" class="Person"/>
      </set>
      <bag name="Documents" order-by="created_date desc" inverse="true">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <one-to-many class="PIFDocument"/>
      </bag>
      <bag name="Pictures" order-by="created_date desc" inverse="true">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <one-to-many class="PIFPicture"/>
      </bag>
      <bag name="Comps" order-by="created_date" inverse="true">
         <cache usage="read-write"/>
         <key column="pif_id"/>
         <one-to-many class="Comp"/>
      </bag>
   </class>

   <query name="findPIFsForUser">
      from PIF pif
      where pif.CurrentOwner.Id = ?
      or pif.Id in (
         select a.PIF.Id
         from Activity a
         where a.AssignedTo.Id = ?
      )
      order by pif.CreatedDate
   </query>
</hibernate-mapping>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.mafc.moses.bo">
   <class
      name="Property"
      table="property"
      lazy="false"
   >
      <cache usage="read-write"/>
      <id
         name="Id"
         type="java.lang.Long"
         column="property_id"
      >
         <generator class="identity"/>
      </id>

      <property
         name="CreatedDate"
         column="created_date"
         type="timestamp"
         not-null="true"
         length="19"
      />
      <property
         name="TypeABC"
         column="property_type_abc"
         type="string"
         not-null="true"
      />
      <property
         name="NumBedrooms"
         column="num_bedrooms"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="NumBaths"
         column="num_baths"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="TotalSqFt"
         column="total_sq_ft"
         type="integer"
         not-null="false"
         length="11"
      />
      <property
         name="AboveGroundSqFt"
         column="above_ground_sq_ft"
         type="integer"
         not-null="false"
         length="11"
      />
      <property
         name="BasementSqFt"
         column="basement_sq_ft"
         type="integer"
         not-null="false"
         length="11"
      />
      <property
         name="NumRooms"
         column="num_rooms"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="NumHalfBaths"
         column="num_half_baths"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="Pool"
         column="has_pool"
         type="true_false"
      />
      <property
         name="YearBuilt"
         column="year_built"
         type="integer"
         not-null="false"
         length="11"
      />
      <property
         name="NumAboveGroundStories"
         column="num_above_ground_levels"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="NumFireplaces"
         column="num_fireplaces"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="NumStories"
         column="num_stories"
         type="java.lang.Short"
         not-null="false"
         length="6"
      />
      <property
         name="FurnaceAge"
         column="furnace_age"
         type="integer"
         not-null="false"
         length="11"
      />
      <property
         name="CrawlSpace"
         column="has_crawl_space"
         type="true_false"
      />
      <property
         name="OilTank"
         column="has_oil_tank"
         type="true_false"
      />
      <property
         name="Well"
         column="has_well"
         type="true_false"
      />
      <property
         name="SepticTank"
         column="has_septic_tank"
         type="true_false"
      />
      <property
         name="AssociationFeePerMonth"
         column="association_fee_per_month"
         type="big_decimal"
         not-null="false"
         length="6"
      />
      <property
         name="ReceiveNewsletter"
         column="receive_newsletter"
         type="true_false"
      />
      <property
         name="Subdivision"
         column="subdivision_name"
         type="string"
         not-null="false"
      />
      <property
         name="SpecialView"
         column="special_view"
         type="string"
         not-null="false"
      >
      </property>

      <component name="PublicRecord" class="PublicRecord">
         <property
            name="TaxId"
            column="pr_tax_id"
            type="string"
            not-null="false"
         />
         <property
            name="CO"
            column="pr_co"
            type="string"
            not-null="false"
         />
         <property
            name="LegalSub"
            column="pr_legal_sub"
            type="string"
            not-null="false"
         />
         <property
            name="LotNumber"
            column="pr_lot_number"
            type="string"
            not-null="false"
         />
         <property
            name="BlockSquare"
            column="pr_block_square"
            type="string"
            not-null="false"
         />
         <property
            name="Section"
            column="pr_section"
            type="string"
            not-null="false"
         />
         <property
            name="TotalTax"
            column="pr_total_tax"
            type="big_decimal"
            not-null="false"
         />
         <property
            name="GrossLivingArea"
            column="pr_gross_living_area"
            type="java.lang.Short"
            not-null="false"
         />
      </component>

      <many-to-one
         name="ExteriorType"
         column="exterior_type"
         class="ExteriorType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="CurrentPIF"
         column="current_pif_id"
         class="PIF"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="ParkingType"
         column="parking_type"
         class="ParkingType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="SidingType"
         column="siding_type"
         class="SidingType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="Type"
         column="property_type"
         class="PropertyType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="AssociationType"
         column="association_type"
         class="AssociationType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="BasementType"
         column="basement_type"
         class="BasementType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="BasementFinish"
         column="basement_finish"
         class="BasementFinish"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="ACType"
         column="air_conditioning_type"
         class="AirConditioningType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="GarageType"
         column="garage_type"
         class="GarageType"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
      <many-to-one
         name="Address"
         column="address_id"
         class="Address"
         not-null="true"
         lazy="false"
         fetch="join"
      >
      </many-to-one>
      <many-to-one
         name="Market"
         column="market_id"
         class="Market"
         not-null="false"
         lazy="false"
      >
      </many-to-one>
   </class>   
</hibernate-mapping>

Generated SQL (from the HQL above... notice that the property stuff isn't in there)

Code:

select pif0_.pif_id as pif1_, pif0_.reservation_nbr as reservat2_63_, pif0_.created_date as created3_63_, pif0_.list_price as list4_63_, pif0_.list_date as list5_63_, pif0_.accept_price as accept6_63_, pif0_.acquisition_amount as acquisit7_63_, pif0_.mortgage_remaining_balance as mortgage8_63_, pif0_.is_current_in_payments as is9_63_, pif0_.repair_roof as repair10_63_, pif0_.repair_interior_paint as repair11_63_, pif0_.repair_exterior_paint as repair12_63_, pif0_.repair_carpet as repair13_63_, pif0_.repair_furnace as repair14_63_, pif0_.repair_ac as repair15_63_, pif0_.repair_windows as repair16_63_, pif0_.repair_cabinets as repair17_63_, pif0_.repair_stove as repair18_63_, pif0_.repair_refridgerator as repair19_63_, pif0_.other_repairs as other20_63_, pif0_.other_improvements as other21_63_, pif0_.vacate_date as vacate22_63_, pif0_.outsale_amount as outsale23_63_, pif0_.outsale_deposit_amount as outsale24_63_, pif0_.property_id as property25_63_, pif0_.contact_person_id as contact26_63_, pif0_.current_owner_id as current27_63_, pif0_.current_valuation_id as current28_63_, pif0_.requested_valuation_id as requested29_63_, pif0_.current_acquisition_offer_id as current30_63_, pif0_.current_outsale_offer_id as current31_63_, pif0_.termite_inspection_id as termite32_63_, pif0_.hvac_inspection_id as hvac33_63_, pif0_.mold_inspection_id as mold34_63_, pif0_.home_inspection_id as home35_63_, pif0_.drive_by_inspection_id as drive36_63_, pif0_.occupancy_type as occupancy37_63_, pif0_.property_condition as property38_63_, pif0_.mls_status as mls39_63_, pif0_.property_stage as property40_63_, pif0_.property_sub_stage as property41_63_, pif0_.lease_type as lease42_63_, pif0_.sell_reason as sell43_63_, pif0_.advertising_type as adverti44_63_, pif0_.dead_deal_reason as dead45_63_ from pif pif0_ where (pif0_.property_stage not in  ('DDL' , 'SLD')) and (pif0_.current_owner_id=? or pif0_.pif_id in (select activity1_.pif_id from activity activity1_ where activity1_.assigned_to_id=?)) limit ?

Any ideas or hints would be greatly appreciated. Thank you very much!

LaLiLuna · **Posted:** Wed Jan 11, 2006 7:30 pm

have a look in the reference at 7.4. Bidirectional associations
you have a many-to-one on both side. This lead to two columns managing the relation, one in pif and one in attribute. I do not know if this is an issue.

A part from this try
select pif from PIF pif left join fetch Activity a
where pif.id in ..
where pif.CurrentOwner.Id = ?
or pif.Id in (
select a.PIF.Id
from Activity a
where a.AssignedTo.Id = ?
)
order by pif.CreatedDate

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

Hi,

Seems like LaLiLuna has a point. You have PIF many-to-one Property and also Property many-to-one PIF. That does not seem right. Maybe the mapping for Property should be Property one-to-many PIF.

-Mrtz