-->
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
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
jbax
 Post subject: Unexpected number of SQL statements on left outer join
PostPosted: Mon Apr 05, 2004 10:33 am 
Newbie

Joined: Mon Apr 05, 2004 9:51 am
Posts: 3
Hello,

I am using Hibernate 2.1 with j2sdk1.4.2_02 atop of Oracle 9i. Everything is working as advertised however I am having trouble performance tuning (reducing the number of SQL statements generated) using left outer joins. I have included all source, the mapping files and the log file output with debug on, at the end of this post

Here is the scenario:

ClassA has an attribute which is a list of ClassB objects. Each ClassB object has an attribute which is a list of ClassC objects. I have successfully used Hibernate to populate 3 ClassA objects, each of which contains 2 ClassB objects which in turn each contain 3 class C objects When I issue the HQL statement

String findString =
"from ClassA as classA " +
"join fetch classA.listOfClassB as listOfClassB " +
"join fetch listOfClassB.listOfClassC as listOfClassC";

List molList = s.find(findString);


Hibernate produces a single SQL statement that contains all the columns required to realise the graph of objects EXCEPT (seemingly) the index column for the collection of ClassB objects in ClassA. Hibernate then issues an additional SQL statement for each ClassA object but this additional query seems to be getting the attributes of the ClassB instances contained within ClassA. Again (seemingly) the only column in these subsequent queries that is not present in the initial query is the index column for ClassB.

So the questions I have are:

1. Is it possible to have Hibernate realize a collection of objects as described above using a single SQL statement (or at least not issuing additional SQL statements equivalent to the the number of ClassA objects returned)

2. If it is possible to have Hibernate do this what do I need to change to make it happen? I have searched through the forums, the documentation and the web. I have tried several different query approaches (HQL and Criteria) but cannot seem to deduce why the additional SQL statements per object in the list of ClassA should be necessary?

The obvious ramification of not being able to solve this issue is that as soon as the list of ClassA objects returned gets even moderately large (>100) performance is going to grind to a halt

Thanks in advance for any help (code, mappings etc below)

Cheers,

James


Code:
package eager;
import java.util.*;

public class ClassA extends Persistent {
  private List listOfClassB;
  public ClassA() {
  }

  public void setListOfClassB(List aList) {
    listOfClassB = aList;
  }

  public List getListOfClassB() {
    return listOfClassB;
  }

}


Code:
package eager;
import java.util.*;

public class ClassB extends Persistent {
  private List listOfClassC;

  public ClassB() {
  }

  public void setListOfClassC(List aList) {
    listOfClassC = aList;
  }

  public List getListOfClassC() {
    return listOfClassC;
  }
}



Code:
package eager;

public class ClassC extends Persistent {
  public ClassC() {
  }
}



Code:
package eager;

public class Persistent {
  private Long id;
  private String someName;
  private String someDesc;

  public void setId(Long anId) {
    id = anId;
  }

  public Long getId() {
    return id;
  }

  public Persistent() {
  }

  public void setSomeName(String aName) {
    someName = aName;
  }

  public String getSomeName() {
    return someName;
  }

  public void setSomeDesc(String aDesc) {
    someDesc = aDesc;
  }

  public String getSomeDesc() {
    return someDesc;
  }

}


Code:
package eager;

import net.sf.hibernate.*;
import net.sf.hibernate.cfg.*;
import net.sf.hibernate.expression.*;
import java.io.*;
import java.util.*;

public class Main {

  private SessionFactory factory;

  public Main() {
  }

  public void loadExampleClassAInstances() throws Exception {
    Session s = factory.openSession();
    Transaction tx = s.beginTransaction();

    ClassA aClassA;
    ClassB aClassB;
    ClassC aClassC;

    // Create 3 ClassA instances each of which
    // contain a list of 2 classB instances which in turn
    // contain a list of 3 classC instances...
    List listOfClassB;
    List listOfClassC;
    for (int i = 0; i < 3; i++) {
      aClassA = new ClassA();
      aClassA.setSomeName("" + i);
      listOfClassB = new ArrayList();
      for (int j = 0; j < 2; j++) {
        aClassB = new ClassB();
        aClassB.setSomeName("" + i + j);
        listOfClassB.add(aClassB);
        listOfClassC = new ArrayList();
        for (int k = 0; k < 3; k++) {
          aClassC = new ClassC();
          aClassC.setSomeName("" + i + j + k);
          listOfClassC.add(aClassC);
          s.save(aClassC);
        }
        aClassB.setListOfClassC(listOfClassC);
        s.save(aClassB);
      }
      aClassA.setListOfClassB(listOfClassB);
      s.save(aClassA);
    }
    tx.commit();
    s.close();
  }

  public void retrieveExampleClassAInstances() throws Exception {
    Session s = factory.openSession();
    Transaction tx = s.beginTransaction();


    String findString =
        // First try
//        "from ClassA as classA, ClassB as classB " +
//        "left join fetch classA.listOfClassB " +
//        "left join fetch classB.listOfClassC";

        // second try
        "from ClassA as classA " +
        "join fetch classA.listOfClassB as listOfClassB " +
        "join fetch listOfClassB.listOfClassC as listOfClassC";

        // third try
//        "from ClassA as classA " +
//        "join fetch classA.listOfClassB";
    List molList = null;

    molList = s.find(findString);
    System.out.println("Number of ClassA's is: " + molList.size());

    // Put the results in a set to remove the duplicates that come from
    // the efficiency of a single SQL statement to retrieve Class As list
    // of Bs and then each Bs list of Cs through joins
    Set molSet = new HashSet(molList);
//    molList = s.createCriteria(ClassA.class)
//        .setFetchMode("listOfClassB", FetchMode.EAGER)


    ClassA aClassA;
    List listOfClassB;
    ClassB aClassB;
    List listOfClassC;
    ClassC aClassC;
    for (Iterator iter = molSet.iterator(); iter.hasNext(); ) {
      aClassA = (ClassA)iter.next();
      listOfClassB = aClassA.getListOfClassB();
      System.out.println("Got a class A instance with name " +
        aClassA.getSomeName() + " and a list of class b instances: " +
        listOfClassB);
      for (Iterator bInstIter = listOfClassB.iterator(); bInstIter.hasNext(); ) {
        aClassB = (ClassB)bInstIter.next();
        listOfClassC = aClassB.getListOfClassC();
        System.out.println("Got a class B instance with name " +
          aClassB.getSomeName() + " and a list of class c instances: " +
          listOfClassC);

      }
    }
    tx.commit();
    s.close();
  }

  public static void main(String[] args) throws Exception {
    final Main test = new Main();

    // This should cause hibernate to try and create the tables
    // that will underly the clases for which the hibernate mapping
    // files exist
    Configuration cfg = new Configuration()
            .addClass(ClassA.class)
            .addClass(ClassB.class)
            .addClass(ClassC.class)
            .setProperty(Environment.HBM2DDL_AUTO, "create");

    test.factory=cfg.buildSessionFactory();
    test.loadExampleClassAInstances();

    test.retrieveExampleClassAInstances();

    test.factory.close();

  }

}



Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
  "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
  "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="eager">
  <class name="ClassA" table="Molecule_Example" proxy="ClassA">
    <id name="id">
      <generator class="native"/>
    </id>
    <property name="someName" not-null="true"/>
    <property name="someDesc"/>
      <list name="listOfClassB">
        <key column="mol_id"/>
        <index column="tt_idx"/>
        <one-to-many class="ClassB"/>
      </list>
  </class>
</hibernate-mapping>


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

  <class name="ClassB" table="Top_Tor_Example" proxy="ClassB">
    <id name="id">
      <generator class="native"/>
    </id>
    <property name="someName" not-null="true"/>
    <property name="someDesc"/>
    <list name="listOfClassC">
      <key column="tt_id"/>
      <index column="tt_val_idx"/>
      <one-to-many class="ClassC"/>
    </list>
  </class>
</hibernate-mapping>


Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
  "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
  "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="eager">
  <class name="ClassC" table="Top_Tor_Val_Example" proxy="ClassC">
    <id name="id">
      <generator class="native"/>
    </id>
    <property name="someName" not-null="true"/>
    <property name="someDesc"/>
  </class>
</hibernate-mapping>



The log file is as follows (notice the 3 additional queries):

09:36:29,632 INFO Environment:462 - Hibernate 2.1.2

09:36:29,822 INFO Environment:496 - loaded properties from resource hibernate.properties: {hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver, hibernate.cglib.use_reflection_optimizer=true, hibernate.cache.provider_class=net.sf.hibernate.cache.HashtableCacheProvider, hibernate.cache.use_query_cache=true, hibernate.max_fetch_depth=1, hibernate.dialect=net.sf.hibernate.dialect.OracleDialect, hibernate.jdbc.use_streams_for_binary=true, hibernate.jdbc.batch_size=0, hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N', hibernate.proxool.pool_alias=pool1, hibernate.connection.username=myusername, hibernate.connection.url=jdbc:oracle:thin:@MY_HOST:MY_PORT:MY_INSTANCE, hibernate.show_sql=true, hibernate.connection.password=mypassword, hibernate.connection.pool_size=1}

09:36:29,862 INFO Environment:518 - using java.io streams to persist binary types

09:36:29,872 INFO Environment:519 - using CGLIB reflection optimizer

09:36:29,882 INFO Configuration:329 - Mapping resource: eager/ClassA.hbm.xml

09:36:30,403 INFO Binder:229 - Mapping class: eager.ClassA -> Molecule_Example

09:36:30,613 INFO Configuration:329 - Mapping resource: eager/ClassB.hbm.xml

09:36:30,694 INFO Binder:229 - Mapping class: eager.ClassB -> Top_Tor_Example

09:36:30,704 INFO Configuration:329 - Mapping resource: eager/ClassC.hbm.xml

09:36:30,734 INFO Binder:229 - Mapping class: eager.ClassC -> Top_Tor_Val_Example

09:36:30,734 INFO Configuration:595 - processing one-to-many association mappings

09:36:30,784 INFO Binder:1154 - Mapping collection: eager.ClassA.listOfClassB -> Top_Tor_Example

09:36:30,794 INFO Binder:1154 - Mapping collection: eager.ClassB.listOfClassC -> Top_Tor_Val_Example

09:36:30,794 INFO Configuration:604 - processing one-to-one association property references

09:36:30,794 INFO Configuration:629 - processing foreign key constraints

09:36:30,904 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.OracleDialect

09:36:30,904 INFO SettingsFactory:58 - Maximim outer join fetch depth: 1

09:36:30,904 INFO SettingsFactory:62 - Use outer join fetching: true

09:36:30,914 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)

09:36:30,914 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1

09:36:31,264 INFO DriverManagerConnectionProvider:71 - using driver: oracle.jdbc.driver.OracleDriver at URL: jdbc:oracle:thin:@MY_HOST:MY_PORT:MY_INSTANCE

09:36:31,264 INFO DriverManagerConnectionProvider:72 - connection properties: {user=myusername, password=mypassword}

09:36:31,274 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)

09:36:32,706 INFO SettingsFactory:102 - Use scrollable result sets: true

09:36:32,706 INFO SettingsFactory:105 - Use JDBC3 getGeneratedKeys(): false

09:36:32,706 INFO SettingsFactory:108 - Optimize cache for minimal puts: false

09:36:32,706 INFO SettingsFactory:114 - echoing all SQL to stdout

09:36:32,706 INFO SettingsFactory:117 - Query language substitutions: {no='N', true=1, yes='Y', false=0}

09:36:32,716 INFO SettingsFactory:128 - cache provider: net.sf.hibernate.cache.HashtableCacheProvider

09:36:32,716 INFO Configuration:1080 - instantiating and configuring caches

09:36:33,037 INFO SessionFactoryImpl:119 - building session factory

09:36:34,098 INFO SessionFactoryObjectFactory:82 - no JNDI name configured

09:36:34,128 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.OracleDialect

09:36:34,128 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)

09:36:34,128 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1

09:36:34,139 INFO DriverManagerConnectionProvider:71 - using driver: oracle.jdbc.driver.OracleDriver at URL: jdbc:oracle:thin:@MY_HOST:MY_PORT:MY_INSTANCE

09:36:34,169 INFO DriverManagerConnectionProvider:72 - connection properties: {user=myusername, password=mypassword}

09:36:34,169 INFO SchemaUpdate:102 - Running hbm2ddl schema update

09:36:34,169 INFO SchemaUpdate:110 - fetching database metadata

09:36:34,379 INFO SchemaUpdate:124 - updating schema

09:36:34,379 INFO Configuration:595 - processing one-to-many association mappings

09:36:34,379 INFO Configuration:604 - processing one-to-one association property references

09:36:34,379 INFO Configuration:629 - processing foreign key constraints

09:36:37,974 INFO SchemaUpdate:143 - schema update complete

09:36:37,984 INFO DriverManagerConnectionProvider:137 - cleaning up connection pool: jdbc:oracle:thin:@MY_HOST:MY_PORT:MY_INSTANCE

09:36:38,004 INFO UpdateTimestampsCache:35 - starting update timestamps cache at region: net.sf.hibernate.cache.UpdateTimestampsCache

09:36:38,004 INFO QueryCache:39 - starting query cache at region: net.sf.hibernate.cache.QueryCache

Hibernate: select classa0_.id as id0_, listofclas1_.id as id1_, listofclas2_.id as id2_, classa0_.someName as someName0_, classa0_.someDesc as someDesc0_, listofclas1_.someName as someName1_, listofclas1_.someDesc as someDesc1_, listofclas2_.someName as someName2_, listofclas2_.someDesc as someDesc2_, listofclas2_.id as id__, listofclas2_.tt_id as tt_id__, listofclas2_.tt_val_idx as tt_val_idx__ from Molecule_Example classa0_, Top_Tor_Example listofclas1_, Top_Tor_Val_Example listofclas2_ where classa0_.id=listofclas1_.mol_id and listofclas1_.id=listofclas2_.tt_id

Hibernate: select listofclas0_.id as id__, listofclas0_.mol_id as mol_id__, listofclas0_.tt_idx as tt_idx__, listofclas0_.id as id0_, listofclas0_.someName as someName0_, listofclas0_.someDesc as someDesc0_ from Top_Tor_Example listofclas0_ where listofclas0_.mol_id=?

Hibernate: select listofclas0_.id as id__, listofclas0_.mol_id as mol_id__, listofclas0_.tt_idx as tt_idx__, listofclas0_.id as id0_, listofclas0_.someName as someName0_, listofclas0_.someDesc as someDesc0_ from Top_Tor_Example listofclas0_ where listofclas0_.mol_id=?

Hibernate: select listofclas0_.id as id__, listofclas0_.mol_id as mol_id__, listofclas0_.tt_idx as tt_idx__, listofclas0_.id as id0_, listofclas0_.someName as someName0_, listofclas0_.someDesc as someDesc0_ from Top_Tor_Example listofclas0_ where listofclas0_.mol_id=?

Number of ClassA's is: 18

Got a class A instance with name 0 and a list of class b instances: [eager.ClassB@126f827, eager.ClassB@1690ab]

Got a class B instance with name 00 and a list of class c instances: [eager.ClassC@16dfa45, eager.ClassC@1dc0e7a, eager.ClassC@3a9bba]

Got a class B instance with name 01 and a list of class c instances: [eager.ClassC@173ec72, eager.ClassC@1a85d38, eager.ClassC@8046f4]

Got a class A instance with name 2 and a list of class b instances: [eager.ClassB@ba4211, eager.ClassB@9ffe3f]

Got a class B instance with name 20 and a list of class c instances: [eager.ClassC@47a0d4, eager.ClassC@8ff4cf, eager.ClassC@1114460]

Got a class B instance with name 21 and a list of class c instances: [eager.ClassC@1ed1b0b, eager.ClassC@18a8ce2, eager.ClassC@12789d2]

Got a class A instance with name 1 and a list of class b instances: [eager.ClassB@d05c13, eager.ClassB@15dd910]

Got a class B instance with name 10 and a list of class c instances: [eager.ClassC@14d1d41, eager.ClassC@1bbd7b2, eager.ClassC@14df764]

Got a class B instance with name 11 and a list of class c instances: [eager.ClassC@55bb93, eager.ClassC@8de462, eager.ClassC@18ce14a]

09:36:39,296 INFO SessionFactoryImpl:531 - closing

09:36:39,296 INFO DriverManagerConnectionProvider:137 - cleaning up connection pool: jdbc:oracle:thin:@MY_HOST:MY_PORT:MY_INSTANCE
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Apr 05, 2004 11:46 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
As per the documentation, it is not possible to fetch two collection roles in a single query. You can use two "ordinary" joins, just not two "fetch" joins.
Top
 Profile  
 
jbax
 Post subject:
PostPosted: Mon Apr 05, 2004 12:11 pm 
Newbie

Joined: Mon Apr 05, 2004 9:51 am
Posts: 3
Thanks for the prompt reply Gavin.

I do recall the statement you mention in the documentation but my reading of it left me unsure as to exactly which scenarios this referred to. For example, did this mean only one collection from a containing class that had more than one collection attribute or did it mean one collection removed from the class being loaded (as was the case for the example I posted) when attempting to load to a depth of more then 1 in the object graph?

From your reply I assume that this means the restriction applies in both cases above and in all cases where a collection is to be loaded along with instances of the class that contains that collection.

So my follow on questions are these:

1. Could the same effect be produced by issuing two Hibernate queries, the first retrieving into the cache the collection of ClassA objects along with the ClassB object instance in the collections they contain and then a second query providing the identifiers of the ClassB objects returned in the first step as a means to asking for a load of all the ClassC instances for each ClassB object.

Would Hibernate knit the object graph of these two queries together correctly assuming both queries were issued from within the same Session?

Are there any mechanisms in Hibernate that would let me get a hold of the identifiers of all the ClassB instances loaded into the cache up to a particular point in time? This would make constructing the second query a lot easier.

2. Any plans to support retrieval of collections to a depth of > 1 in the future?

3. If the answer to 2 is no could you explain why this restriction is necessary? Is it just a lack of resources to apply to the problem or is there something inherently restrictive to solving this problem?

Thanks again for your help and thanks to the Hibernate team for putting together an excellent and much needed OO to RDBMS mapping tool.

Cheers,

James
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Apr 05, 2004 12:28 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
(1) yes, but if you are going to go this route, why not just let Hibernate do it for you by setting batch-size.


(2) not really, though I can see that this is a perfectly legitimate query. Unlike the case of fetching two collection roles from the same class, this is not vulnerable to cartesian products.

(3) it is quite an uncommon thing to want to do
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
  [ 4 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.