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
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
weliwarmer
 Post subject: Loading of child Set very slow
PostPosted: Thu Dec 09, 2004 6:19 am 
Newbie

Joined: Thu Dec 09, 2004 4:26 am
Posts: 19
Location: Yorkshire, UK
Yet another one-to-many question.....

I'm pretty sure that the mapping is setup correctly since it works but is very slow.

I have two classes, ELR and TRID with a parent/child relationship with ELR as the parent.

When the session loads a sample ELR, I see the SQL for the ELR followed by the 1000 or so TRID's go past, very slowly. About 2 statements a second.

I tried lazy loading but I'm porting this from a legacy app that knows nothing of the levels below it so when it does elr.getTRIDs(), the session has gone. I don't want to recode the level above to reattach the beans to a new session, so TRIDs need eager loading.

The legacy app takes about 15 seconds to load the TRIDS into cached objects. The hibernate one takes about 10 minutes! I must be doing something wrong.

Thanks for any suggestions,
Tim

Hibernate version:
2.1.7

Mapping documents:
Code:
<hibernate-mapping>

        <class name="omnieng.burnbaby.beans.ELR" table="elr" mutable="false">
                <id name="name" column="elr_name" type="string">
                   <generator class="increment"/>
                </id>
            <set name="trids" inverse="true" lazy="false">
                <key>
                    <column name="elrt_elr_name"/>
                </key>
                <one-to-many class="omnieng.burnbaby.beans.TRID"/>
            </set>
        </class>

</hibernate-mapping>

Code:
<hibernate-mapping>

        <class name="omnieng.burnbaby.beans.TRID" table="elrtrid" mutable="false">
                <composite-id>
                   <key-property  name="id" column="elrt_trid"/>
                   <key-property  name="elrName" column="elrt_elr_name"/>
                </composite-id>
                <many-to-one name="elr" column="elrt_elr_name" class="omnieng.burnbaby.beans.ELR" insert="false" update="false" outer-join="true"/>
        </class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Code:
Transaction tx = session.beginTransaction();

ELR elr = (ELR)session.get( ELR.class, name );
           
tx.commit();


Name and version of the database you are using:
Oracle 9i

The generated SQL (show_sql=true):
Code:
Hibernate: select elr0_.elr_name as elr_name0_ from elr elr0_ where elr0_.elr_name=?
Hibernate: select trids0_.elrt_elr_name as elrt_elr2___, trids0_.elrt_trid as elrt_trid__, trids0_.elrt_trid as elrt_trid0_, trids0_.elrt_elr_name as elrt_elr2_0_ from elrtrid trids0_ where trids0_.elrt_elr_name=?
Hibernate: select trid0_.elrt_trid as elrt_trid1_, trid0_.elrt_elr_name as elrt_elr2_1_, elr1_.elr_name as elr_name0_ from elrtrid trid0_ left outer join elr elr1_ on trid0_.elrt_elr_name=elr1_.elr_name where trid0_.elrt_trid=? and trid0_.elrt_elr_name=?
Hibernate: select trid0_.elrt_trid as elrt_trid1_, trid0_.elrt_elr_name as elrt_elr2_1_, elr1_.elr_name as elr_name0_ from elrtrid trid0_ left outer join elr elr1_ on trid0_.elrt_elr_name=elr1_.elr_name where trid0_.elrt_trid=? and trid0_.elrt_elr_name=?
Hibernate: selec..... and so on

Debug level Hibernate log excerpt:
Code:
[DEBUG,SessionImpl] opened session
[DEBUG,JDBCTransaction] begin
[DEBUG,JDBCTransaction] current autocommit status:false
[DEBUG,SessionImpl] loading [omnieng.burnbaby.beans.ELR#MLN1]

[DEBUG,SessionImpl] attempting to resolve [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,SessionImpl] object not resolved in any cache [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,EntityPersister] Materializing entity: [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
[DEBUG,SQL] select elr0_.elr_name as elr_name0_ from elr elr0_ where elr0_.elr_name=?
[DEBUG,BatcherImpl] preparing statement
[DEBUG,StringType] binding 'MLN1' to parameter: 1
[DEBUG,Loader] processing result set
[DEBUG,Loader] result row: MLN1
[DEBUG,Loader] Initializing object from ResultSet: MLN1
[DEBUG,Loader] Hydrating entity: omnieng.burnbaby.beans.ELR#MLN1
[DEBUG,Loader] done processing result set (1 rows)
[DEBUG,BatcherImpl] done closing: 0 open PreparedStatements, 0 open ResultSets
[DEBUG,BatcherImpl] closing statement
[DEBUG,Loader] total objects hydrated: 1
[DEBUG,SessionImpl] resolving associations for [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,SessionImpl] creating collection wrapper:[omnieng.burnbaby.beans.ELR.trids#MLN1]
[DEBUG,SessionImpl] done materializing entity [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,SessionImpl] initializing non-lazy collections
[DEBUG,SessionImpl] initializing collection [omnieng.burnbaby.beans.ELR.trids#MLN1]
[DEBUG,SessionImpl] checking second-level cache
[DEBUG,SessionImpl] collection not cached
[DEBUG,BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
[DEBUG,SQL] select trids0_.elrt_elr_name as elrt_elr2___, trids0_.elrt_trid as elrt_trid__, trids0_.elrt_trid as elrt_trid0_, trids0_.elrt_elr_name as elrt_elr2_0_ from elrtrid trids0_ where trids0_.elrt_elr_name=?
[DEBUG,BatcherImpl] preparing statement
[DEBUG,StringType] binding 'MLN1' to parameter: 1
[DEBUG,Loader] result set contains (possibly empty) collection: [omnieng.burnbaby.beans.ELR.trids#MLN1]
[DEBUG,SessionImpl] uninitialized collection: initializing
[DEBUG,Loader] processing result set
[DEBUG,StringType] returning '1100' as column: elrt_trid0_
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2_0_
[DEBUG,Loader] result row: omnieng.burnbaby.beans.TRID@5e7020
[DEBUG,Loader] Initializing object from ResultSet: omnieng.burnbaby.beans.TRID@5e7020
[DEBUG,Loader] Hydrating entity: omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@5e7020
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2_0_
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2___
[DEBUG,Loader] found row of collection: [omnieng.burnbaby.beans.ELR.trids#MLN1]
[DEBUG,SessionImpl] reading row
[DEBUG,StringType] returning '1100' as column: elrt_trid__
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2___
[DEBUG,SessionImpl] loading [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@135daf]
[DEBUG,SessionImpl] attempting to resolve [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@135daf]
[DEBUG,SessionImpl] object not resolved in any cache [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@135daf]
[DEBUG,EntityPersister] Materializing entity: [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@135daf]
[DEBUG,BatcherImpl] about to open: 1 open PreparedStatements, 1 open ResultSets
[DEBUG,SQL] select trid0_.elrt_trid as elrt_trid1_, trid0_.elrt_elr_name as elrt_elr2_1_, elr1_.elr_name as elr_name0_ from elrtrid trid0_ left outer join elr elr1_ on trid0_.elrt_elr_name=elr1_.elr_name where trid0_.elrt_trid=? and trid0_.elrt_elr_name=?
[DEBUG,BatcherImpl] preparing statement
[DEBUG,StringType] binding '1100' to parameter: 1
[DEBUG,StringType] binding 'MLN1' to parameter: 2
[DEBUG,Loader] processing result set
[DEBUG,StringType] returning 'MLN1' as column: elr_name0_
[DEBUG,Loader] result row: MLN1, omnieng.burnbaby.beans.TRID@135daf
[DEBUG,Loader] Initializing object from ResultSet: omnieng.burnbaby.beans.TRID@135daf
[DEBUG,Loader] Hydrating entity: omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@135daf
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2_1_
[DEBUG,Loader] done processing result set (1 rows)
[DEBUG,BatcherImpl] done closing: 1 open PreparedStatements, 1 open ResultSets
[DEBUG,BatcherImpl] closing statement
[DEBUG,Loader] total objects hydrated: 1
[DEBUG,SessionImpl] resolving associations for [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@135daf]
[DEBUG,SessionImpl] loading [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,SessionImpl] attempting to resolve [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,SessionImpl] resolved object in session cache [omnieng.burnbaby.beans.ELR#MLN1]
[DEBUG,SessionImpl] done materializing entity [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@31c6ace]
[DEBUG,StringType] returning '1101' as column: elrt_trid0_
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2_0_
[DEBUG,Loader] result row: omnieng.burnbaby.beans.TRID@1f243d1
[DEBUG,Loader] Initializing object from ResultSet: omnieng.burnbaby.beans.TRID@1f243d1
[DEBUG,Loader] Hydrating entity: omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@1f243d1
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2_0_
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2___
[DEBUG,Loader] found row of collection: [omnieng.burnbaby.beans.ELR.trids#MLN1]
[DEBUG,SessionImpl] reading row
[DEBUG,StringType] returning '1101' as column: elrt_trid__
[DEBUG,StringType] returning 'MLN1' as column: elrt_elr2___
[DEBUG,SessionImpl] loading [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@425eb9]
[DEBUG,SessionImpl] attempting to resolve [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@425eb9]
[DEBUG,SessionImpl] object not resolved in any cache [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@425eb9]
[DEBUG,EntityPersister] Materializing entity: [omnieng.burnbaby.beans.TRID#omnieng.burnbaby.beans.TRID@425eb9]
[DEBUG,BatcherImpl] about to open: 1 open PreparedStatements, 1 open ResultSets
[DEBUG,SQL] select trid0_...... and so on
Top
 Profile  
 
weliwarmer
 Post subject: Composite key class speeds it up
PostPosted: Thu Dec 09, 2004 7:17 am 
Newbie

Joined: Thu Dec 09, 2004 4:26 am
Posts: 19
Location: Yorkshire, UK
I was trying not to use one but replacing the two keys in TRID (id, elrName) with a key class (TRIDId) and all TRIDs are returned in a single query...

A query time of less than one second. Lovely.

I happy now. Hibernate the King.

New mapping
Code:
<hibernate-mapping>

        <class name="omnieng.burnbaby.beans.TRID" table="elrtrid" mutable="false">
                <composite-id name="key" class="omnieng.burnbaby.beans.TRIDId">
                   <key-property  name="id" column="elrt_trid"/>
                   <key-property  name="elrName" column="elrt_elr_name"/>
                </composite-id>
                <many-to-one name="elr" column="elrt_elr_name" class="omnieng.burnbaby.beans.ELR" insert="false" update="false" not-null="true"/>
        </class>

</hibernate-mapping>


Key class
Code:
public class TRIDId implements Serializable
{

    private String id;
    private String elrName;
   
    public TRIDId( String id, String elrName )
    {
        this.id = id;
        this.elrName = elrName;
    }

    /**
     * Required for hibernate to work.
     * A no arg constructor.
     *
     */
    protected TRIDId()
    {
    }

    /**
     * @return Returns the elrName.
     */
    public String getElrName()
    {
        return elrName;
    }
    /**
     * @param elrName The elrName to set.
     */
    public TRIDId setElrName(String elrName)
    {
        this.elrName = elrName;
        return this;
    }
    /**
     * @return Returns the id.
     */
    public String getId()
    {
        return id;
    }
    /**
     * @param id The id to set.
     */
    public TRIDId setId(String id)
    {
        this.id = id;
        return this;
    }
   /* (non-Javadoc)
    * @see java.lang.Object#equals(java.lang.Object)
    */
   public boolean equals( Object other )
    {
        if ( this == other )
            return true;
        if ( !( other instanceof TRIDId ) )
            return false;
        final TRIDId that = (TRIDId)other;
        return this.getId().equals( that.getId() );
    }

    /* (non-Javadoc)
     * @see java.lang.Object#hashCode()
     */
    public int hashCode()
    {
        if ( id == null || elrName == null )
            return System.identityHashCode( this );
       
        int result = 44;
        result = 33 * result + getId().hashCode();
        result = 33 * result + getElrName().hashCode();
        return result;
    }
}



SQL Generated
Code:
Hibernate: select elr0_.elr_name as elr_name0_ from elr elr0_ where elr0_.elr_name=?
Hibernate: select trids0_.elrt_elr_name as elrt_elr2___, trids0_.elrt_trid as elrt_trid__, trids0_.elrt_trid as elrt_trid0_, trids0_.elrt_elr_name as elrt_elr2_0_ from elrtrid trids0_ where trids0_.elrt_elr_name=?
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
  [ 2 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.