-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
monkeymagoo
 Post subject: Join performance in HQL
PostPosted: Thu Apr 12, 2007 10:43 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:38 am
Posts: 22
Hi

I've just tried to improve performance for a select on quite a large object which has lots of links to other tables.

I was originally using session.get() but I've changed it to use a HQL query with lots of "left join fetch" statements instead.

I thought Hibernate would combine all of these joins into one large SQL query. But, when I look at the debug, it seems to do one select per join.

Am I using HQL wrong, or is this expected behaviour? Here's my mapping and query.

Thanks

Code:
from ProductValue pv left join fetch pv.pool left join fetch pv.productGroup left join fetch pv.productStatus left join fetch pv.regulatedBy left join fetch pv.productSubType left join fetch pv.paymentFrequency left join fetch pv.redemptionPenalty where pv.productId = :productId



Code:
<class name="com.lsb.uk.mqs.value.ProductValue" table="mortgage_plans">

        <id name="productId" column="PLAN_ID" unsaved-value="0">
            <generator class="sequence">
                <param name="sequence">plan_id_seq</param>
            </generator>
        </id>

        <property name="description" column="DESCRIPTION"/>
        <property name="standardConstruction" column="STANDARD_CONSTRUCTION"/>
        <property name="minPropertyValue" column="MIN_PROPERTY_VALUE"/>
        <property name="minAge" column="MIN_AGE"/>
        <property name="maxAge" column="MAX_AGE"/>
        <property name="minTermsMonths" column="MIN_TERM_MONTHS"/>
        <property name="loanToValue" column="LOAN_TO_VALUE"/>
        <property name="minLoan" column="MIN_LOAN"/>
        <property name="maxLoan" column="MAX_LOAN"/>
        <property name="incomeMultiple" column="INCOME_MULTIPLE"/>
        <property name="primaryProduct" column="SUBORDINATE_LEVEL"/>
        <property name="ltvBasedOnGrossLoan" column="LTV_BASED_ON_GROSS_LOAN"/>
        <property name="interestRate" column="INTEREST_RATE" type="nullbigdecimal"/>
        <property name="incomeTest1" column="INCOME_TEST_1"/>
        <property name="incomeTest2" column="INCOME_TEST_2"/>
        <property name="dayOfMonth" column="DAY_OF_MONTH"/>

        <many-to-one name="pool"
                     class="com.lsb.uk.mqs.value.PoolValue"
                     column="POOL_ID"
                     fetch="join"
                     not-null="true"/>

        <many-to-one name="productGroup"
                     class="com.lsb.uk.mqs.value.ProductGroupValue"
                     column="PRODUCT_GROUP_ID"
                     fetch="join"
                     not-null="true"/>

        <property name="productCode" column="PRODUCT_CODE"/>
        <property name="coolingOffPeriod" column="COOLING_OFF_PERIOD"/>
        <property name="automaticCancellationArrears" column="AUTOMATIC_CANCELLATION_ARREARS"/>
        <property name="paymentOffset" column="PAYMENT_OFFSET"/>

        <many-to-one name="productStatus"
                     class="com.lsb.uk.mqs.value.GeneralRefLabelValue"
                     column="PRODUCT_STATUS_ID"
                     fetch="join"
                     not-null="true"/>

        <many-to-one name="regulatedBy"
                     class="com.lsb.uk.mqs.value.GeneralRefLabelValue"
                     column="REGULATED_BY_ID"
                     fetch="join"/>

        <many-to-one name="productSubType"
                     class="com.lsb.uk.mqs.value.ProductSubTypeValue"
                     column="PRODUCT_SUBTYPE_ID"
                     fetch="join"
                     outer-join="true"/>

        <many-to-one name="paymentFrequency"
                     class="com.lsb.uk.mqs.value.GeneralRefLabelValue"
                     column="PAYMENT_FREQUENCY_ID"
                     fetch="join"/>

        <many-to-one name="redemptionPenalty"
                     class="com.lsb.uk.mqs.value.RedemptionPenaltyValue"
                     column="REDEMPTION_PENALTY_ID"
                     fetch="join"/>

        <property name="lastUpdateUser" column="LAST_MODIFIED_BY"/>
        <property name="lastUpdateDate" column="LAST_MODIFIED_ON"/>
    </class>
Top
 Profile  
 
roger_rf
 Post subject:
PostPosted: Thu Apr 12, 2007 10:56 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:43 am
Posts: 21
Location: Brazil
Hi monkeymagoo,
Does this happen for every <many-to-one> element on every line of the MORTAGE_PLANS table? Or only for certain <many-to-one> elements on certain lines? Also, are you using native or assigned generators on the tables you're joining with? Cheers,
Roger
Top
 Profile  
 
hoeft
 Post subject:
PostPosted: Thu Apr 12, 2007 11:14 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 3:26 am
Posts: 35
Location: Germany
Hi!

With fetch="join" eager fetching in a join is activated. You are using this in your mapping meta data. Hence your entites are already eagerly fetched, the hql statement is redundant:

Code:
from ProductValue pv pv.pool ...


In this statement the "left join fetch" string are removed but the referenced entities are still eagerly loaded due to the fetch attribute in the meta data. With this statement, you should only see one sql statement.

Hoeft



[/code]
Top
 Profile  
 
monkeymagoo
 Post subject:
PostPosted: Thu Apr 12, 2007 11:17 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:38 am
Posts: 22
Hi Roger

Thanks for the reply.

I've taken a look at the debug a bit more closely and the huge query I'm expecting is actually being generated correctly (see the first line). I think this is all I need Hibernate to do for me. But then it goes off and at least logs select statements for each relationship.

The many-to-many mappings are linked via their primary keys, although the primary key is not necessarily a sequence - not sure if that makes a difference. For example, the ID for ProductSubTypeValue is defined simply as:

<id name="id" column="product_subtype_id" unsaved-value="0"/>

(I guess because it's read-only data - the code is never going to input new values into this table)

The other thing I noticed is that each select is being performed at least twice (including the first correct one). Do you think this is just overly-verbose logging on Hibernate's part, or is it really executing each one twice?

Code:
DEBUG hibernate.SQL             - select productval0_.PLAN_ID as PLAN1_34_0_, poolvalue1_.POOL_ID as POOL1_35_1_, productgro2_.PRODUCT_GROUP_ID as PRODUCT1_41_2_, generalref3_.GENERAL_REFERENCE_ID as GENERAL1_44_3_, generalref4_.GENERAL_REFERENCE_ID as GENERAL1_44_4_, productsub5_.product_subtype_id as product1_43_5_, generalref6_.GENERAL_REFERENCE_ID as GENERAL1_44_6_, redemption7_.ID as ID36_7_, productval0_.DESCRIPTION as DESCRIPT2_34_0_, productval0_.STANDARD_CONSTRUCTION as STANDARD3_34_0_, productval0_.MIN_PROPERTY_VALUE as MIN4_34_0_, productval0_.MIN_AGE as MIN5_34_0_, productval0_.MAX_AGE as MAX6_34_0_, productval0_.MIN_TERM_MONTHS as MIN7_34_0_, productval0_.LOAN_TO_VALUE as LOAN8_34_0_, productval0_.MIN_LOAN as MIN9_34_0_, productval0_.MAX_LOAN as MAX10_34_0_, productval0_.INCOME_MULTIPLE as INCOME11_34_0_, productval0_.SUBORDINATE_LEVEL as SUBORDI12_34_0_, productval0_.LTV_BASED_ON_GROSS_LOAN as LTV13_34_0_, productval0_.INTEREST_RATE as INTEREST14_34_0_, productval0_.INCOME_TEST_1 as INCOME15_34_0_, productval0_.INCOME_TEST_2 as INCOME16_34_0_, productval0_.DAY_OF_MONTH as DAY17_34_0_, productval0_.POOL_ID as POOL18_34_0_, productval0_.PRODUCT_GROUP_ID as PRODUCT19_34_0_, productval0_.PRODUCT_CODE as PRODUCT20_34_0_, productval0_.COOLING_OFF_PERIOD as COOLING21_34_0_, productval0_.AUTOMATIC_CANCELLATION_ARREARS as AUTOMATIC22_34_0_, productval0_.PAYMENT_OFFSET as PAYMENT23_34_0_, productval0_.PRODUCT_STATUS_ID as PRODUCT24_34_0_, productval0_.REGULATED_BY_ID as REGULATED25_34_0_, productval0_.PRODUCT_SUBTYPE_ID as PRODUCT26_34_0_, productval0_.PAYMENT_FREQUENCY_ID as PAYMENT27_34_0_, productval0_.REDEMPTION_PENALTY_ID as REDEMPTION28_34_0_, productval0_.LAST_MODIFIED_BY as LAST29_34_0_, productval0_.LAST_MODIFIED_ON as LAST30_34_0_, productval0_1_.MAX_MONTH_COVER as MAX2_37_0_, productval0_1_.MAX_LUMPSUM_COVER as MAX3_37_0_, productval0_1_.LAST_MODIFIED_BY as LAST4_37_0_, productval0_1_.LAST_MODIFIED_ON as LAST5_37_0_, productval0_1_.INSURER_ID as INSURER6_37_0_, case when productval0_1_.PRODUCT_ID is not null then 1 when productval0_.PLAN_ID is not null then 0 end as clazz_0_, poolvalue1_.POOL_CODE as POOL2_35_1_, poolvalue1_.LIBOR as LIBOR35_1_, productgro2_.DESCRIPTION as DESCRIPT2_41_2_, productgro2_.ROLE as ROLE41_2_, generalref3_.DESCRIPTION as DESCRIPT2_44_3_, generalref3_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_44_3_, generalref3_.MAS_GENERAL_REFERENCE_ID as MAS4_44_3_, generalref4_.DESCRIPTION as DESCRIPT2_44_4_, generalref4_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_44_4_, generalref4_.MAS_GENERAL_REFERENCE_ID as MAS4_44_4_, productsub5_.description as descript2_43_5_, generalref6_.DESCRIPTION as DESCRIPT2_44_6_, generalref6_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_44_6_, generalref6_.MAS_GENERAL_REFERENCE_ID as MAS4_44_6_, redemption7_.DESCRIPTION as DESCRIPT2_36_7_, redemption7_.ONGOING_CHARGE as ONGOING3_36_7_, redemption7_.YEAR1_CHARGE as YEAR4_36_7_, redemption7_.YEAR1_PERCENTAGE as YEAR5_36_7_, redemption7_.YEAR2_CHARGE as YEAR6_36_7_, redemption7_.YEAR2_PERCENTAGE as YEAR7_36_7_, redemption7_.YEAR3_CHARGE as YEAR8_36_7_, redemption7_.YEAR3_PERCENTAGE as YEAR9_36_7_, redemption7_.YEAR4_CHARGE as YEAR10_36_7_, redemption7_.YEAR4_PERCENTAGE as YEAR11_36_7_, redemption7_.YEAR5_CHARGE as YEAR12_36_7_, redemption7_.YEAR5_PERCENTAGE as YEAR13_36_7_, redemption7_.YEAR6_CHARGE as YEAR14_36_7_, redemption7_.YEAR6_PERCENTAGE as YEAR15_36_7_, redemption7_.YEAR7_CHARGE as YEAR16_36_7_, redemption7_.YEAR7_PERCENTAGE as YEAR17_36_7_, redemption7_.YEAR8_CHARGE as YEAR18_36_7_, redemption7_.YEAR8_PERCENTAGE as YEAR19_36_7_, redemption7_.LAST_MODIFIED_BY as LAST20_36_7_, redemption7_.LAST_MODIFIED_ON as LAST21_36_7_ from mortgage_plans productval0_ left outer join PRODUCT_PPI productval0_1_ on productval0_.PLAN_ID=productval0_1_.PRODUCT_ID left outer join POOL poolvalue1_ on productval0_.POOL_ID=poolvalue1_.POOL_ID left outer join product_group productgro2_ on productval0_.PRODUCT_GROUP_ID=productgro2_.PRODUCT_GROUP_ID left outer join GENERAL_REFERENCE generalref3_ on productval0_.PRODUCT_STATUS_ID=generalref3_.GENERAL_REFERENCE_ID left outer join GENERAL_REFERENCE generalref4_ on productval0_.REGULATED_BY_ID=generalref4_.GENERAL_REFERENCE_ID left outer join product_subtype productsub5_ on productval0_.PRODUCT_SUBTYPE_ID=productsub5_.product_subtype_id left outer join GENERAL_REFERENCE generalref6_ on productval0_.PAYMENT_FREQUENCY_ID=generalref6_.GENERAL_REFERENCE_ID left outer join redemption_penalties redemption7_ on productval0_.REDEMPTION_PENALTY_ID=redemption7_.ID where productval0_.PLAN_ID=?

DEBUG hibernate.SQL             - select productval0_.PLAN_ID as PLAN1_34_0_, poolvalue1_.POOL_ID as POOL1_35_1_, productgro2_.PRODUCT_GROUP_ID as PRODUCT1_41_2_, generalref3_.GENERAL_REFERENCE_ID as GENERAL1_44_3_, generalref4_.GENERAL_REFERENCE_ID as GENERAL1_44_4_, productsub5_.product_subtype_id as product1_43_5_, generalref6_.GENERAL_REFERENCE_ID as GENERAL1_44_6_, redemption7_.ID as ID36_7_, productval0_.DESCRIPTION as DESCRIPT2_34_0_, productval0_.STANDARD_CONSTRUCTION as STANDARD3_34_0_, productval0_.MIN_PROPERTY_VALUE as MIN4_34_0_, productval0_.MIN_AGE as MIN5_34_0_, productval0_.MAX_AGE as MAX6_34_0_, productval0_.MIN_TERM_MONTHS as MIN7_34_0_, productval0_.LOAN_TO_VALUE as LOAN8_34_0_, productval0_.MIN_LOAN as MIN9_34_0_, productval0_.MAX_LOAN as MAX10_34_0_, productval0_.INCOME_MULTIPLE as INCOME11_34_0_, productval0_.SUBORDINATE_LEVEL as SUBORDI12_34_0_, productval0_.LTV_BASED_ON_GROSS_LOAN as LTV13_34_0_, productval0_.INTEREST_RATE as INTEREST14_34_0_, productval0_.INCOME_TEST_1 as INCOME15_34_0_, productval0_.INCOME_TEST_2 as INCOME16_34_0_, productval0_.DAY_OF_MONTH as DAY17_34_0_, productval0_.POOL_ID as POOL18_34_0_, productval0_.PRODUCT_GROUP_ID as PRODUCT19_34_0_, productval0_.PRODUCT_CODE as PRODUCT20_34_0_, productval0_.COOLING_OFF_PERIOD as COOLING21_34_0_, productval0_.AUTOMATIC_CANCELLATION_ARREARS as AUTOMATIC22_34_0_, productval0_.PAYMENT_OFFSET as PAYMENT23_34_0_, productval0_.PRODUCT_STATUS_ID as PRODUCT24_34_0_, productval0_.REGULATED_BY_ID as REGULATED25_34_0_, productval0_.PRODUCT_SUBTYPE_ID as PRODUCT26_34_0_, productval0_.PAYMENT_FREQUENCY_ID as PAYMENT27_34_0_, productval0_.REDEMPTION_PENALTY_ID as REDEMPTION28_34_0_, productval0_.LAST_MODIFIED_BY as LAST29_34_0_, productval0_.LAST_MODIFIED_ON as LAST30_34_0_, productval0_1_.MAX_MONTH_COVER as MAX2_37_0_, productval0_1_.MAX_LUMPSUM_COVER as MAX3_37_0_, productval0_1_.LAST_MODIFIED_BY as LAST4_37_0_, productval0_1_.LAST_MODIFIED_ON as LAST5_37_0_, productval0_1_.INSURER_ID as INSURER6_37_0_, case when productval0_1_.PRODUCT_ID is not null then 1 when productval0_.PLAN_ID is not null then 0 end as clazz_0_, poolvalue1_.POOL_CODE as POOL2_35_1_, poolvalue1_.LIBOR as LIBOR35_1_, productgro2_.DESCRIPTION as DESCRIPT2_41_2_, productgro2_.ROLE as ROLE41_2_, generalref3_.DESCRIPTION as DESCRIPT2_44_3_, generalref3_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_44_3_, generalref3_.MAS_GENERAL_REFERENCE_ID as MAS4_44_3_, generalref4_.DESCRIPTION as DESCRIPT2_44_4_, generalref4_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_44_4_, generalref4_.MAS_GENERAL_REFERENCE_ID as MAS4_44_4_, productsub5_.description as descript2_43_5_, generalref6_.DESCRIPTION as DESCRIPT2_44_6_, generalref6_.GENERAL_REFERENCE_GROUP_ID as GENERAL3_44_6_, generalref6_.MAS_GENERAL_REFERENCE_ID as MAS4_44_6_, redemption7_.DESCRIPTION as DESCRIPT2_36_7_, redemption7_.ONGOING_CHARGE as ONGOING3_36_7_, redemption7_.YEAR1_CHARGE as YEAR4_36_7_, redemption7_.YEAR1_PERCENTAGE as YEAR5_36_7_, redemption7_.YEAR2_CHARGE as YEAR6_36_7_, redemption7_.YEAR2_PERCENTAGE as YEAR7_36_7_, redemption7_.YEAR3_CHARGE as YEAR8_36_7_, redemption7_.YEAR3_PERCENTAGE as YEAR9_36_7_, redemption7_.YEAR4_CHARGE as YEAR10_36_7_, redemption7_.YEAR4_PERCENTAGE as YEAR11_36_7_, redemption7_.YEAR5_CHARGE as YEAR12_36_7_, redemption7_.YEAR5_PERCENTAGE as YEAR13_36_7_, redemption7_.YEAR6_CHARGE as YEAR14_36_7_, redemption7_.YEAR6_PERCENTAGE as YEAR15_36_7_, redemption7_.YEAR7_CHARGE as YEAR16_36_7_, redemption7_.YEAR7_PERCENTAGE as YEAR17_36_7_, redemption7_.YEAR8_CHARGE as YEAR18_36_7_, redemption7_.YEAR8_PERCENTAGE as YEAR19_36_7_, redemption7_.LAST_MODIFIED_BY as LAST20_36_7_, redemption7_.LAST_MODIFIED_ON as LAST21_36_7_ from mortgage_plans productval0_ left outer join PRODUCT_PPI productval0_1_ on productval0_.PLAN_ID=productval0_1_.PRODUCT_ID left outer join POOL poolvalue1_ on productval0_.POOL_ID=poolvalue1_.POOL_ID left outer join product_group productgro2_ on productval0_.PRODUCT_GROUP_ID=productgro2_.PRODUCT_GROUP_ID left outer join GENERAL_REFERENCE generalref3_ on productval0_.PRODUCT_STATUS_ID=generalref3_.GENERAL_REFERENCE_ID left outer join GENERAL_REFERENCE generalref4_ on productval0_.REGULATED_BY_ID=generalref4_.GENERAL_REFERENCE_ID left outer join product_subtype productsub5_ on productval0_.PRODUCT_SUBTYPE_ID=productsub5_.product_subtype_id left outer join GENERAL_REFERENCE generalref6_ on productval0_.PAYMENT_FREQUENCY_ID=generalref6_.GENERAL_REFERENCE_ID left outer join redemption_penalties redemption7_ on productval0_.REDEMPTION_PENALTY_ID=redemption7_.ID where productval0_.PLAN_ID=?



DEBUG hibernate.SQL             - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_45_0_, generalref0_.DESCRIPTION as DESCRIPT2_45_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?
DEBUG hibernate.SQL             - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_45_0_, generalref0_.DESCRIPTION as DESCRIPT2_45_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?
DEBUG hibernate.SQL             - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_45_0_, generalref0_.DESCRIPTION as DESCRIPT2_45_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?
DEBUG hibernate.SQL             - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_45_0_, generalref0_.DESCRIPTION as DESCRIPT2_45_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?
DEBUG hibernate.SQL             - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_45_0_, generalref0_.DESCRIPTION as DESCRIPT2_45_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?
DEBUG hibernate.SQL             - select generalref0_.GENERAL_REFERENCE_GROUP_ID as GENERAL1_45_0_, generalref0_.DESCRIPTION as DESCRIPT2_45_0_ from GENERAL_REFERENCE_GROUP generalref0_ where generalref0_.GENERAL_REFERENCE_GROUP_ID=?



DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id

DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id


DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?

DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?


DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id
DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id
DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id
DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id


DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?


DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id
DEBUG hibernate.SQL             - select productsub0_.product_group_id as product1_1_, productsub0_.product_subtype_id as product2_1_, productsub1_.product_subtype_id as product1_43_0_, productsub1_.description as descript2_43_0_ from product_group_subtype productsub0_ left outer join product_subtype productsub1_ on productsub0_.product_subtype_id=productsub1_.product_subtype_id where productsub0_.product_group_id=? order by productsub0_.product_subtype_id


DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
DEBUG hibernate.SQL             - select productgro0_.product_subtype_id as product2_1_, productgro0_.product_group_id as product1_1_, productgro1_.PRODUCT_GROUP_ID as PRODUCT1_41_0_, productgro1_.DESCRIPTION as DESCRIPT2_41_0_, productgro1_.ROLE as ROLE41_0_ from product_group_subtype productgro0_ left outer join product_group productgro1_ on productgro0_.product_group_id=productgro1_.PRODUCT_GROUP_ID where productgro0_.product_subtype_id=?
Top
 Profile  
 
monkeymagoo
 Post subject:
PostPosted: Thu Apr 12, 2007 11:43 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:38 am
Posts: 22
Thanks Hoeft, I think I was trying to be too clever (something I've never been good at).

So, I've simplified the query down to a meagre 'from ProductValue pv where pv.productId = :productId'

I also changed PoolValue to be lazy="true" and this stopped a couple of other duplicate selects being generated.

...and this seems to bring everything back exactly the way I want it, and in only 2 queries

DEBUG hibernate.SQL - select productval0_.PLAN_ID as PLAN1_34_, productval0_.DESCRIPTION as DESCRIPT2_34_, productval0_.STANDARD_CONSTRUCTION as STANDARD3_34_, productval0_.MIN_PROPERTY_VALUE as MIN4_34_, productval0_.MIN_AGE as MIN5_34_, productval0_.MAX_AGE as MAX6_34_, productval0_.MIN_TERM_MONTHS as MIN7_34_, productval0_.LOAN_TO_VALUE as LOAN8_34_, productval0_.MIN_LOAN as MIN9_34_, productval0_.MAX_LOAN as MAX10_34_, productval0_.INCOME_MULTIPLE as INCOME11_34_, productval0_.SUBORDINATE_LEVEL as SUBORDI12_34_, productval0_.LTV_BASED_ON_GROSS_LOAN as LTV13_34_, productval0_.INTEREST_RATE as INTEREST14_34_, productval0_.INCOME_TEST_1 as INCOME15_34_, productval0_.INCOME_TEST_2 as INCOME16_34_, productval0_.DAY_OF_MONTH as DAY17_34_, productval0_.POOL_ID as POOL18_34_, productval0_.PRODUCT_GROUP_ID as PRODUCT19_34_, productval0_.PRODUCT_CODE as PRODUCT20_34_, productval0_.COOLING_OFF_PERIOD as COOLING21_34_, productval0_.AUTOMATIC_CANCELLATION_ARREARS as AUTOMATIC22_34_, productval0_.PAYMENT_OFFSET as PAYMENT23_34_, productval0_.PRODUCT_STATUS_ID as PRODUCT24_34_, productval0_.REGULATED_BY_ID as REGULATED25_34_, productval0_.PRODUCT_SUBTYPE_ID as PRODUCT26_34_, productval0_.PAYMENT_FREQUENCY_ID as PAYMENT27_34_, productval0_.REDEMPTION_PENALTY_ID as REDEMPTION28_34_, productval0_.LAST_MODIFIED_BY as LAST29_34_, productval0_.LAST_MODIFIED_ON as LAST30_34_, productval0_1_.MAX_MONTH_COVER as MAX2_37_, productval0_1_.MAX_LUMPSUM_COVER as MAX3_37_, productval0_1_.LAST_MODIFIED_BY as LAST4_37_, productval0_1_.LAST_MODIFIED_ON as LAST5_37_, productval0_1_.INSURER_ID as INSURER6_37_, case when productval0_1_.PRODUCT_ID is not null then 1 when productval0_.PLAN_ID is not null then 0 end as clazz_ from mortgage_plans productval0_ left outer join PRODUCT_PPI productval0_1_ on productval0_.PLAN_ID=productval0_1_.PRODUCT_ID where productval0_.PLAN_ID=?

DEBUG hibernate.SQL - select productval0_.PLAN_ID as PLAN1_34_, productval0_.DESCRIPTION as DESCRIPT2_34_, productval0_.STANDARD_CONSTRUCTION as STANDARD3_34_, productval0_.MIN_PROPERTY_VALUE as MIN4_34_, productval0_.MIN_AGE as MIN5_34_, productval0_.MAX_AGE as MAX6_34_, productval0_.MIN_TERM_MONTHS as MIN7_34_, productval0_.LOAN_TO_VALUE as LOAN8_34_, productval0_.MIN_LOAN as MIN9_34_, productval0_.MAX_LOAN as MAX10_34_, productval0_.INCOME_MULTIPLE as INCOME11_34_, productval0_.SUBORDINATE_LEVEL as SUBORDI12_34_, productval0_.LTV_BASED_ON_GROSS_LOAN as LTV13_34_, productval0_.INTEREST_RATE as INTEREST14_34_, productval0_.INCOME_TEST_1 as INCOME15_34_, productval0_.INCOME_TEST_2 as INCOME16_34_, productval0_.DAY_OF_MONTH as DAY17_34_, productval0_.POOL_ID as POOL18_34_, productval0_.PRODUCT_GROUP_ID as PRODUCT19_34_, productval0_.PRODUCT_CODE as PRODUCT20_34_, productval0_.COOLING_OFF_PERIOD as COOLING21_34_, productval0_.AUTOMATIC_CANCELLATION_ARREARS as AUTOMATIC22_34_, productval0_.PAYMENT_OFFSET as PAYMENT23_34_, productval0_.PRODUCT_STATUS_ID as PRODUCT24_34_, productval0_.REGULATED_BY_ID as REGULATED25_34_, productval0_.PRODUCT_SUBTYPE_ID as PRODUCT26_34_, productval0_.PAYMENT_FREQUENCY_ID as PAYMENT27_34_, productval0_.REDEMPTION_PENALTY_ID as REDEMPTION28_34_, productval0_.LAST_MODIFIED_BY as LAST29_34_, productval0_.LAST_MODIFIED_ON as LAST30_34_, productval0_1_.MAX_MONTH_COVER as MAX2_37_, productval0_1_.MAX_LUMPSUM_COVER as MAX3_37_, productval0_1_.LAST_MODIFIED_BY as LAST4_37_, productval0_1_.LAST_MODIFIED_ON as LAST5_37_, productval0_1_.INSURER_ID as INSURER6_37_, case when productval0_1_.PRODUCT_ID is not null then 1 when productval0_.PLAN_ID is not null then 0 end as clazz_ from mortgage_plans productval0_ left outer join PRODUCT_PPI productval0_1_ on productval0_.PLAN_ID=productval0_1_.PRODUCT_ID where productval0_.PLAN_ID=?


If you could explain why the main query is being logged twice I would be very grateful, although this is already a vast improvement. (I tried lazy="true" on the ProductValue class btw, and it still logs twice)

Thanks again for everyones' help.
Top
 Profile  
 
monkeymagoo
 Post subject:
PostPosted: Thu Apr 12, 2007 12:00 pm 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:38 am
Posts: 22
BTW if it helps anyone new to Hibernate, the reason I was getting unstuck was mainly due to the incorrect assumption that HQL would kind of override any settings laid out in the object mapping. E.g. a fetch="join" in the mapping would be ignored by the HQL.
Top
 Profile  
 
roger_rf
 Post subject:
PostPosted: Thu Apr 12, 2007 1:11 pm 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:43 am
Posts: 21
Location: Brazil
Hi monkeymagoo,

When I use assigned generators, I always declare them with [unsaved-value="none"]. That way, Hibernate will never hit the DB to check whether an object is synchronized with the data store, which would require extra SELECT's. This is a design decision, and it's up to you to decide which behavior is best for your application.

Also, every SELECT that shows up in your logs is actually being executed by Hibernate, so yes, the query is being performed twice!

HQL doesn't really override the mapping settings, I believe the only exception is LEFT JOIN FETCH. Those settings are way more important if you use the Criteria API, though. I hardly ever use lazy attribute fetching, and I tend to stick to LEFT JOIN FETCH and [fetch="join"] to avoid surprises.

Does every line in MORTGAGE_PLANS contain valid values for the foreign keys? If, say, MORTGAGE_PLANS.REGULATED_BY_ID contains the value '7', but there is no matching record in the foreign table, if you try to access the members of [regulatedBy] Hibernate might try to fetch the data by SELECT'ing from the foreign table.

Could you try redeclaring MORTGAGE_PLANS id attribute to the settings below? When you use [class="native"] there is no need to declare [unsaved_value].

<id name="productId" column="PLAN_ID">
<generator class="native">
<param name="sequence">plan_id_seq</param>
</generator>
</id>

Cheers,
Roger
Top
 Profile  
 
richardgundersen
 Post subject:
PostPosted: Fri Apr 13, 2007 4:36 am 
Beginner
Beginner

Joined: Fri Nov 24, 2006 10:33 am
Posts: 42
Hi Roger

Thanks again for the tips. Very useful and interesting. I've tried the things you suggested: setting the unsaved-value to none (and making sure the java doesn't use saveOrUpdate), and also trying native generator which I think will fall back to use a sequence in my Oracle environment.

I'm still not able to reduce the remaining two identical select statements down to one which is my ultimate goal, but nevertheless thanks for the info because I now know quite a bit more about how Hibernate works!

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

Board index » Hibernate & Java Persistence » Hibernate Users

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.