-->
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
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
darkcube
 Post subject: many-to-one relationship performance issues
PostPosted: Mon Jul 16, 2007 6:33 pm 
Newbie

Joined: Mon Jul 16, 2007 6:18 pm
Posts: 2
I needed to map a many-to-one association to a table but it needs to map on two different columns neither of which are the primary key, and one of the values must be specified manually.

So this is what i came up with:

Code:
<many-to-one name="providerTypeCodeset" property-ref="providerCodeset">
    <column name="PROV_TYPE_CODE" length="5"/>
    <formula>'00002'</formula>
</many-to-one>


which maps to:

Code:
<hibernate-mapping>
    <class name="ProviderCodeset" table="PROV_CS" schema="PROV">
        <cache usage="read-write"/>
        <id name="providerCodesetKey" type="java.lang.Integer">
            <column name="PROV_CS_KEY" />
            <generator class="assigned" />
        </id>
        <properties name="providerCodeset">
           <property name="providerCodesetCode" type="java.lang.String">
               <column name="PROV_CS_CODE" length="5" />
           </property>
           <property name="providerCodesetTypeCode" type="java.lang.String">
               <column name="PROV_CS_TYPE_CODE" length="5" />
           </property>
       </properties>
        <property name="sourceApplicationCode" type="java.lang.String">
            <column name="SRC_APPL_CODE" length="5" />
        </property>
        <property name="activeIndicator" type="java.lang.Integer">
            <column name="ACTV_IND" />
        </property>
        <property name="sortOrderNumber" type="java.lang.Integer">
            <column name="SORT_ORDR_NBR" />
        </property>
        <property name="providerCommonCodesetCode" type="java.lang.String">
            <column name="PROV_CMN_CS_CODE" length="5" />
        </property>
        <property name="providerCodesetName" type="java.lang.String">
            <column name="PROV_CS_NAME" length="100" />
        </property>
        <property name="providerCodesetDescription" type="java.lang.String">
            <column name="PROV_CS_DESC" length="1000" />
        </property>
    </class>
</hibernate-mapping>


The problem now is that i cannot get the relationship to lazy load, nor can i get the codesets to cache using ehcache. so i'm getting an n+1 performance problem. I cannot change the data model, is there a better way to map this or to cache the codeset table?
Top
 Profile  
 
amila733
 Post subject:
PostPosted: Tue Jul 17, 2007 3:25 am 
Senior
Senior

Joined: Thu May 17, 2007 2:31 am
Posts: 194
Location: Sri Lanka
Hi

I don't know exactly what you want. there are few methods in my mind. I can say exact solution if you send more details. What are the other mapping classes and mapping xmls and db structure.


Amila
Top
 Profile  
 
darkcube
 Post subject:
PostPosted: Tue Jul 17, 2007 9:53 am 
Newbie

Joined: Mon Jul 16, 2007 6:18 pm
Posts: 2
The biggest problem i'd like to solve is that the codeset associations are not lazy loading, a mapping for providers for example:

Code:
<hibernate-mapping>
    <class name="Provider" table="PROV" schema="PROV">
       <cache usage="read-write"/>
        <id name="providerKey" type="java.lang.Integer">
            <column name="PROV_KEY" />
            <generator class="assigned" />
        </id>
        <property name="effectiveDate" type="java.util.Date">
            <column name="EFF_DATE" length="10" />
        </property>
        <property name="doingBusinessAsName" type="java.lang.String">
            <column name="DNGBSNSAS_NAME" length="100" />
        </property>
        <property name="facilityIndicator" type="java.lang.Integer">
            <column name="FAC_IND" />
        </property>
        <property name="legalName" type="java.lang.String">
            <column name="LGL_NAME" length="100" />
        </property>
        <many-to-one lazy="false" fetch="join" name="providerClassificationCodeset" property-ref="providerCodeset">
           <column name="PROV_CLS_CODE" length="5"/>
           <formula>'00003'</formula>
        </many-to-one>
        <many-to-one lazy="false" fetch="join" name="providerStatusCodeset" property-ref="providerCodeset">
           <column name="PROV_STS_CODE" length="5"/>
           <formula>'00001'</formula>
        </many-to-one>
        <many-to-one lazy="false" fetch="join" name="providerTypeCodeset" property-ref="providerCodeset">
           <column name="PROV_TYPE_CODE" length="5"/>
           <formula>'00002'</formula>
        </many-to-one>
        <set name="practitioners" inverse="true">
            <key>
                <column name="PROV_KEY" />
            </key>
            <one-to-many class="Practitioner" />
        </set>
    </class>
</hibernate-mapping>


If i do a select to get all the providers (there are about 4000 of them), it does a select statement to get the list of providers:

Code:
select
        provider0_.PROV_KEY as PROV1_44_,
        provider0_.EFF_DATE as EFF2_44_,
        provider0_.DNGBSNSAS_NAME as DNGBSNSAS3_44_,
        provider0_.FAC_IND as FAC4_44_,
        provider0_.LGL_NAME as LGL5_44_,
        provider0_.PROV_CLS_CODE as PROV6_44_,
        provider0_.PROV_STS_CODE as PROV7_44_,
        provider0_.PROV_TYPE_CODE as PROV8_44_,
        '00003' as formula0_,
        '00001' as formula1_,
        '00002' as formula2_
    from
        PROV.PROV provider0_


followed by 12,000 select statements to get 3 ProviderCodesets for each provider (3*n):

Code:
select
        providerco0_.PROV_CS_KEY as PROV1_45_0_,
        providerco0_.PROV_CS_CODE as PROV2_45_0_,
        providerco0_.PROV_CS_TYPE_CODE as PROV3_45_0_,
        providerco0_.SRC_APPL_CODE as SRC4_45_0_,
        providerco0_.ACTV_IND as ACTV5_45_0_,
        providerco0_.SORT_ORDR_NBR as SORT6_45_0_,
        providerco0_.PROV_CMN_CS_CODE as PROV7_45_0_,
        providerco0_.PROV_CS_NAME as PROV8_45_0_,
        providerco0_.PROV_CS_DESC as PROV9_45_0_
    from
        PROV.PROV_CS providerco0_
    where
        providerco0_.PROV_CS_CODE=?
        and providerco0_.PROV_CS_TYPE_CODE=?


As bad as this is, the bigger problem is that every single table in the database (around 80 tables) have associations for ProviderCodesets in them.

So my biggest problem is, why arent these associations lazy loading? Reguardless of what i set lazy to, {proxy,no-proxy,false}, they never lazy load. How can i change this so it doesnt load every single association when getting the list? thanks.
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
  [ 3 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.