[b]Hibernate version:[/b]
2.1.6
[b]Mapping documents:[/b]
<?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="be.barc.materialkit.data">
<class name="Material" table="Material">
<id
column="Material_ID"
name="id"
type="java.lang.Long"
>
<generator class="native" />
</id>
<property
column="MaterialCode"
name="code"
not-null="true"
type="java.lang.String"
/>
<property
column="Description"
name="description"
not-null="true"
type="java.lang.String"
/>
<property
column="Price"
name="purchasePrice"
not-null="true"
type="java.lang.Double"
/>

<property
column="ContractPrice"
name="salesPrice"
not-null="true"
type="java.lang.Double"
/>
<property
column="LogonSite_ID"
name="division"
not-null="true"
type="java.lang.Long"
/>
<!-- one to one association with MaterialForKit: see unique foreign key associations p35 -->
<one-to-one
name="material"
class="MaterialForKit"
property-ref="material"
/>
</class>
</hibernate-mapping>

and

<?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="be.barc.materialkit.data">
<class name="MaterialForKit" table="MaterialForKit">
<id name="id" type="java.lang.Long">
<generator class="native" />
</id>
<property
name="available"
column="Available"
not-null="true"
type="java.lang.Integer"
/>
<property
column="CreatedBy"
name="createdBy"
not-null="true"
type="java.lang.Long"
/>
<property
column="CreatedOn"
name="createdOn"
not-null="true"
type="java.util.Date"
/>
<property
column="ModifiedBy"
name="modifiedBy"
not-null="false"
type="java.lang.Long"
/>
<property
column="ModifiedOn"
name="modifiedOn"
not-null="false"
type="java.util.Date"
/>
<property
name="material"
column="Mat_ID"
unique="true"
not-null="true"
type="java.lang.Long"
/>
<set name="materialkitdetails" inverse="true" cascade="all" lazy="true">
<key column="MAT_ID" />
<one-to-many class="MaterialKitDetail" />
</set>
</class>
</hibernate-mapping>
[b]Code between sessionFactory.openSession() and session.close():[/b]
tx = s.beginTransaction();

materials = s.find(
"from Material as material order by material.code");

tx.commit();

[b]Full stack trace of any exception that occurs:[/b]

[b]Name and version of the database you are using:[/b]
MySQL v 3 or 4
[b]Debug level Hibernate log excerpt:[/b]

OK,

via SQuirreL the following query takes about 2 sec (20493 records):

select x.Material_ID, x.Description, y.Available
from Material x LEFT OUTER JOIN MaterialForKit y ON x.Material_ID = y.Mat_ID

If i execute the above mentioned code it takes about 29 sec before i get my List. Is this normal or can this be speeded up? I would like to end up in the region of about 3 - 5 sec?

Show more code.

This should absolutely not take 10 times as long as JDBC. Have you traced what takes so long?

Ok, here is the full code of my classes:

1) method in a datamanager class:
(in the constructor of the class:
Configuration cfg = new Configuration().configure();
factory = cfg.buildSessionFactory();
)

public final List getAllMaterials() throws Exception {
Session s = factory.openSession();
Transaction tx = null;
List materials = null;

try {
tx = s.beginTransaction();

materials = s.find(
"from Material as material order by material.code");

tx.commit();
} catch (Exception e) {
if (tx != null) {
tx.rollback();
}

logCat.error("Error in getAllMaterials: " + e.toString());
throw new Exception("ERR0006");
} finally {
try {
s.close();
} catch (Exception exp) {
logCat.error("Error in getAllMaterials(close): "
+ exp.toString());
}
}

return materials;
}

2) the Material class:
package be.barc.materialkit.data;

import be.barc.materialkit.data.base.BaseMaterial;


/**
* This is the object class that relates to the Material table.
* Any customizations belong here.
*/
public class Material extends BaseMaterial {
/*[CONSTRUCTOR MARKER BEGIN]*/
/**
* Constructor
*/
public Material() {
}

/**
* Constructor for primary key
* @param id the database-id
*/
public Material(final java.lang.Long id) {
super(id);
}

/**
* Constructor for required fields
* @param id the database-id
* @param code the code for the material
* @param description the description for the material
* @param purchasePrice the purchase price
* @param salesPrice the sales price
* @param division the database-id for the division the material belongs to.
*/
public Material(final java.lang.Long id,
final java.lang.String code,
final java.lang.String description,
final java.lang.Double purchasePrice,
final java.lang.Double salesPrice,
final java.lang.Long division) {
super(id, code, description, purchasePrice, salesPrice, division);
}

/*[CONSTRUCTOR MARKER END]*/
/**
* returns the code of the material
* @return the code
*/
public final String toString() {
return this.getCode();
}
}

3) the BaseMaterial class
package be.barc.materialkit.data.base;

import java.io.Serializable;


/**
* This is an object that contains data related to the Material table.
* Do not modify this class because it will be overwritten if
* the configuration file related to this class is modified.
* For more information visit <a href="http://hibernatesynch.sourceforge.net">
* The Hibernate Synchronizer page</a>, or contact
* <a href="mailto: jhudson8.users.sourceforge.net">Joe Hudson</a>
*
* @hibernate.class
* table="Material"
*/
public abstract class BaseMaterial implements Serializable {
/**
* holds the hashcode for the equals-method
*/
private int hashCode = Integer.MIN_VALUE;
/**
* the primary-key
*/
private java.lang.Long id;
/**
* the code of the material
*/
private java.lang.String code;
/**
* the description of the material
*/
private java.lang.String description;
/**
* the purchase price
*/
private java.lang.Double purchasePrice;
/**
* the sales price
*/
private java.lang.Double salesPrice;
/**
* the materialForKit instance that belongs to this material
*/
private be.barc.materialkit.data.MaterialForKit material;
/**
* the division this material belongs to
*/
private java.lang.Long division;
/**
* Constructor
*
*/
public BaseMaterial() {
}
/**
* Constructor for primary key
* @param lId the database-id
*/
public BaseMaterial(final java.lang.Long lId) {
this.setId(lId);
}
/**
* Constructor
* @param lId the database-id
* @param sCode the code
* @param sDescription th edescription
* @param dPurchasePrice the purchase price
* @param dSalesPrice the sales-price
* @param lDivision the division-database-id
*/
public BaseMaterial(final java.lang.Long lId,
final java.lang.String sCode,
final java.lang.String sDescription,
final java.lang.Double dPurchasePrice,
final java.lang.Double dSalesPrice,
final java.lang.Long lDivision) {
this.setId(lId);
this.setDivision(lDivision);
this.setCode(sCode);
this.setDescription(sDescription);
this.setPurchasePrice(dPurchasePrice);
this.setSalesPrice(dSalesPrice);
}
/**
* Return the unique identifier of this class
* @hibernate.id
* generator-class="native"
* column="Material_ID"
* @return the database-id
*/
public final java.lang.Long getId() {
return id;
}
/**
* Set the unique identifier of this class
* @param lId the new ID
*/
public final void setId(final java.lang.Long lId) {
this.id = lId;
this.hashCode = Integer.MIN_VALUE;
}
/**
* @hibernate.property
* column=MaterialCode
* not-null=true
* @return the code
*/
public final java.lang.String getCode() {
return this.code;
}
/**
* Set the value related to the column: MaterialCode
* @param sCode the MaterialCode value
*/
public final void setCode(final java.lang.String sCode) {
this.code = sCode;
}
/**
* @hibernate.property
* column=Description
* not-null=true
* @return the description
*/
public final java.lang.String getDescription() {
return this.description;
}
/**
* Set the value related to the column: Description
* @param sDescription the Description value
*/
public final void setDescription(final java.lang.String sDescription) {
this.description = sDescription;
}
/**
* @hibernate.property
* column=Price
* not-null=true
* @return the purchase price
*/
public final java.lang.Double getPurchasePrice() {
return this.purchasePrice;
}
/**
* Set the value related to the column: Price
* @param dPurchasePrice the Price value
*/
public final void setPurchasePrice(final java.lang.Double dPurchasePrice) {
this.purchasePrice = dPurchasePrice;
}
/**
* @hibernate.property
* column=ContractPrice
* not-null=true
* @return the sales price
*/
public final java.lang.Double getSalesPrice() {
return this.salesPrice;
}
/**
* Set the value related to the column: ContractPrice
* @param dSalesPrice the ContractPrice value
*/
public final void setSalesPrice(final java.lang.Double dSalesPrice) {
this.salesPrice = dSalesPrice;
}
/**
* @hibernate.property
* column=material
* @return the material-object
*/
public final be.barc.materialkit.data.MaterialForKit getMaterial() {
return this.material;
}
/**
* Set the value related to the column: material
* @param mMaterial the material value
*/
public final void setMaterial(
final be.barc.materialkit.data.MaterialForKit mMaterial) {
this.material = mMaterial;
}
/**
* @hibernate.property
* column=LogonSite_ID
* not-null=true
* @return the division-dfatabase-id
*/
public final java.lang.Long getDivision() {
return this.division;
}
/**
* Set the value related to the column: LogonSite_ID
* @param lDivision the LogonSite_ID value
*/
public final void setDivision(final java.lang.Long lDivision) {
this.division = lDivision;
}
/**
* compares this material with another object
* @param obj the object to compare with
* @return true if equals, otherwise false
*/
public final boolean equals(final Object obj) {
if (null == obj) {
return false;
}

if (!(obj instanceof be.barc.materialkit.data.base.BaseMaterial)) {
return false;
} else {
be.barc.materialkit.data.base.BaseMaterial mObj =
(be.barc.materialkit.data.base.BaseMaterial) obj;

if ((null == this.getId()) || (null == mObj.getId())) {
return false;
} else {
return (this.getId().equals(mObj.getId()));
}
}
}
/**
* returns the hashcode for this object
* @return the hashcode
*/
public final int hashCode() {
if (Integer.MIN_VALUE == this.hashCode) {
if (null == this.getId()) {
return super.hashCode();
} else {
String hashStr = this.getClass().getName() + ":"
+ this.getId().hashCode();
this.hashCode = hashStr.hashCode();
}
}

return this.hashCode;
}
}

4) the MaterialForKit class
package be.barc.materialkit.data;

import be.barc.materialkit.data.base.BaseMaterialForKit;


/**
* This is the object class that relates to the MaterialForKit table.
* Any customizations belong here.
*/
public class MaterialForKit extends BaseMaterialForKit {
/*[CONSTRUCTOR MARKER BEGIN]*/
/**
* Constructor
*/
public MaterialForKit() {
}

/**
* Constructor for primary key
* @param id the database-id
*/
public MaterialForKit(final java.lang.Long id) {
super(id);
}

/**
* Constructor for required fields
* @param id the database-id
* @param available if null or 0 = not available, else 1
* @param createdBy the user-id who created the record
* @param createdOn the date the record was created
* @param modifiedBy the user-id who last modified the record
* @param modifiedOn the date the last modification occurred
* @param material the database-id for the material this object
* belongs to.
*/
public MaterialForKit(final java.lang.Long id,
final java.lang.Integer available,
final java.lang.Long createdBy,
final java.util.Date createdOn,
final java.lang.Long modifiedBy,
final java.util.Date modifiedOn,
final java.lang.Long material) {
super(id, available, createdBy, createdOn, modifiedBy, modifiedOn,
material);
}

/*[CONSTRUCTOR MARKER END]*/
}

5) the BaseMaterialForKit class
package be.barc.materialkit.data.base;

import java.io.Serializable;


/**
* This is an object that contains data related to the MaterialForKit table.
* Do not modify this class because it will be overwritten if
* the configuration file related to this class is modified.
* For more information visit <a href="http://hibernatesynch.sourceforge.net">
* The Hibernate Synchronizer page</a>, or contact
* <a href="mailto: jhudson8.users.sourceforge.net">Joe Hudson</a>
*
* @hibernate.class
* table="MaterialForKit"
*/
public abstract class BaseMaterialForKit implements Serializable {
/**
* holds the hascode for th equals-method
*/
private int hashCode = Integer.MIN_VALUE;
/**
* holds the primary-key
*/
private java.lang.Long id;
/**
* sets whether the materail is available (1) or not (null or 0)
*/
private java.lang.Integer available;
/**
* the user-id who created the record
*/
private java.lang.Long createdBy;
/**
* the date the record was created
*/
private java.util.Date createdOn;
/**
* the user-id who last modified the record
*/
private java.lang.Long modifiedBy;
/**
* the date the record was last modified
*/
private java.util.Date modifiedOn;
/**
* the material this object belongs to
*/
private java.lang.Long material;
/**
* the set of details that this object belongs to
*/
private java.util.Set materialkitdetails;
/**
* Constructor
*
*/
public BaseMaterialForKit() {
}
/**
* Constructor for primary key
* @param lId the database-id
*/
public BaseMaterialForKit(final java.lang.Long lId) {
this.setId(lId);
}
/**
* Constructor
* @param lId the database-id
* @param iAvailable indicator whether the material is available
* @param lCreatedBy the user-id who created the record
* @param dCreatedOn the date the record was created
* @param lModifiedBy the user-id who last modified the record
* @param dModifiedOn the date the record was last modified
* @param lMaterial the material this object belongs to
*/
public BaseMaterialForKit(final java.lang.Long lId,
final java.lang.Integer iAvailable,
final java.lang.Long lCreatedBy,
final java.util.Date dCreatedOn,
final java.lang.Long lModifiedBy,
final java.util.Date dModifiedOn,
final java.lang.Long lMaterial) {
this.setId(id);
this.setAvailable(available);
this.setCreatedBy(createdBy);
this.setCreatedOn(createdOn);
this.setModifiedBy(modifiedBy);
this.setModifiedOn(modifiedOn);
this.setMaterial(material);
}
/**
* Return the unique identifier of this class
* @hibernate.id
* generator-class="native"
* column="id"
* @return the database-id
*/
public final java.lang.Long getId() {
return id;
}
/**
* Set the unique identifier of this class
* @param lId the new ID
*/
public final void setId(final java.lang.Long lId) {
this.id = lId;
this.hashCode = Integer.MIN_VALUE;
}
/**
* @hibernate.property
* column=Available
* not-null=true
* @return the available-indicator
*/
public final java.lang.Integer getAvailable() {
return this.available;
}
/**
* Set the value related to the column: Available
* @param iAvailable the Available value
*/
public final void setAvailable(final java.lang.Integer iAvailable) {
this.available = iAvailable;
}
/**
* @hibernate.property
* column=CreatedBy
* not-null=true
* @return the user-id who created the record
*/
public final java.lang.Long getCreatedBy() {
return this.createdBy;
}
/**
* Set the value related to the column: CreatedBy
* @param lCreatedBy the CreatedBy value
*/
public final void setCreatedBy(final java.lang.Long lCreatedBy) {
this.createdBy = lCreatedBy;
}
/**
* @hibernate.property
* column=CreatedOn
* not-null=true
* @return the date the record was created
*/
public final java.util.Date getCreatedOn() {
return this.createdOn;
}
/**
* Set the value related to the column: CreatedOn
* @param dCreatedOn the CreatedOn value
*/
public final void setCreatedOn(final java.util.Date dCreatedOn) {
this.createdOn = dCreatedOn;
}
/**
* @hibernate.property
* column=ModifiedBy
* not-null=true
* @return the user-id who last modified the record
*/
public final java.lang.Long getModifiedBy() {
return this.modifiedBy;
}
/**
* Set the value related to the column: ModifiedBy
* @param lModifiedBy the ModifiedBy value
*/
public final void setModifiedBy(final java.lang.Long lModifiedBy) {
this.modifiedBy = lModifiedBy;
}
/**
* @hibernate.property
* column=ModifiedOn
* not-null=true
* @return the date the record was last modified
*/
public final java.util.Date getModifiedOn() {
return this.modifiedOn;
}
/**
* Set the value related to the column: ModifiedOn
* @param dModifiedOn the ModifiedOn value
*/
public final void setModifiedOn(final java.util.Date dModifiedOn) {
this.modifiedOn = dModifiedOn;
}
/**
* @hibernate.property
* column=Mat_ID
* @return the database-id of the material this object belongs to
*/
public final java.lang.Long getMaterial() {
return this.material;
}
/**
* Set the value related to the column: Mat_ID
* @param lMaterial the Mat_ID value
*/
public final void setMaterial(final java.lang.Long lMaterial) {
this.material = lMaterial;
}
/**
* returns the Set of details this object belongs to
* @return the Set
*/
public final java.util.Set getMaterialkitdetails() {
return this.materialkitdetails;
}
/**
* sets the Set of details that this object belongs to
* @param sMaterialkitdetails a Set-object
*/
public final void setMaterialkitdetails(
final java.util.Set sMaterialkitdetails) {
this.materialkitdetails = sMaterialkitdetails;
}
/**
* adds an object to the set of details
* @param obj a MaterialKitDetail-object
*/
public final void addToMaterialkitdetails(final Object obj) {
if (null == this.materialkitdetails) {
this.materialkitdetails = new java.util.HashSet();
}

this.materialkitdetails.add(obj);
}
/**
* compares this object whith another
* @param obj the object to compare with
* @return true if equals, false otherwise
*/
public final boolean equals(final Object obj) {
if (null == obj) {
return false;
}

if (!(obj
instanceof be.barc.materialkit.data.base.BaseMaterialForKit)) {
return false;
} else {
be.barc.materialkit.data.base.BaseMaterialForKit mObj =
(be.barc.materialkit.data.base.BaseMaterialForKit) obj;

if ((null == this.getId()) || (null == mObj.getId())) {
return false;
} else {
return (this.getId().equals(mObj.getId()));
}
}
}
/**
* returns the hashcode for the equals-method
* @return the hashcode
*/
public final int hashCode() {
if (Integer.MIN_VALUE == this.hashCode) {
if (null == this.getId()) {
return super.hashCode();
} else {
String hashStr = this.getClass().getName() + ":"
+ this.getId().hashCode();
this.hashCode = hashStr.hashCode();
}
}

return this.hashCode;
}
}

All i do is call the method of point 1.

Are you sure you are not measuring with the construction of the SessionFactory? This should happen only once, and not be included in any performance measurment. Please do some tracing and figure out what the time is spent on.

have you turned off log ?

Hi,

first to Michael:
1) I create the factory only once (the datamanager is kept in a singleton-class and is created only on construction of that class - it is only this class that uses the datamanager).
2) how can i do the tracing for something hibernate is doing?

to anthony:
if you mean the 'show sql' switch in the hibernate config file, that is switched off! or do you mean another logging mechanism?

Well, insert some time output into your code and at least figure out what call takes that much time.

Hi Michael,

thats what i did and its this

materials = s.find(
"from Material as material order by material.code");

call that takes the 29 secs to complete.

maybe im missing a statement in my config file???

here it is:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration
PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>
<session-factory>
<!-- ************** -->
<!-- Query Language -->
<!-- ************** -->
<!-- *************************** -->
<!-- local connection properties -->
<!-- *************************** -->
<property name="hibernate.connection.driver_class">
com.mysql.jdbc.Driver
</property>
<property name="hibernate.connection.url">
jdbc:mysql://voulezvous.barclab.com/JavaDev
</property>
<property name="hibernate.connection.username">jdev-adm</property>
<property name="hibernate.connection.password">jdev-adm</property>
<!-- ***************** -->
<!-- dialect for MySQL -->
<!-- ***************** -->
<property name="dialect">
net.sf.hibernate.dialect.MySQLDialect
</property>
<!-- ************************* -->
<!-- Hibernate Connection Pool -->
<!-- ************************* -->
<!-- property name="hibernate.connection.pool_size"></property -->
<!-- ******************** -->
<!-- C3P0 Connection Pool -->
<!-- ******************** -->
<!-- property name="hibernate.c3p0.max_size">2</property -->
<!-- property name="hibernate.c3p0.min_size">2</property -->
<!-- property name="hibernate.c3p0.timeout">5000</property -->
<!-- property name="hibernate.c3p0.max_statements">100</property -->
<!-- property name="hibernate.c3p0.idle_test_period">3000</property -->
<!-- property name="hibernate.c3p0.acquire_increment">2</property -->
<!-- property name="#hibernate.c3p0.validate">false</property -->
<!-- ************************** -->
<!-- Plugin Connection Provider -->
<!-- ************************** -->
<property name="hibernate.connection.provider_class">
net.sf.hibernate.connection.C3P0ConnectionProvider
</property>
<!-- *************** -->
<!-- Transaction API -->
<!-- *************** -->
<property name="hibernate.transaction.factory_class">
<!-- net.sf.hibernate.transaction.JTATransactionFactory -->
net.sf.hibernate.transaction.JDBCTransactionFactory
</property>

<!-- ********************** -->
<!-- Miscellaneous Settings -->
<!-- ********************** -->
<!-- print all generated SQL to the console -->
<property name="hibernate.show_sql">false</property>
<!-- auto schema export -->
<!-- property name="hibernate.hb2ddl.auto">create-drop</property -->
<!-- property name="hibernate.hb2ddl.auto">create</property -->
<!-- property name="hibernate.hb2ddl.auto">update</property -->
<property name="hibernate.use_outer_join">true</property>
<!-- ****************** -->
<!-- Second level cache -->
<!-- ****************** -->
<!--property name="hibernate.cache.use_query_cache">true</property -->
<property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.Provider</property>
<!-- ********************* -->
<!-- MAPPING FILES -->
<!-- ********************* -->
<mapping resource="be/barc/materialkit/data/Material.hbm.xml" />
<mapping resource="be/barc/materialkit/data/MaterialForKit.hbm.xml" />
<mapping resource="be/barc/materialkit/data/MaterialKit.hbm.xml" />
<mapping resource="be/barc/materialkit/data/MaterialKitDetail.hbm.xml" />

</session-factory>
</hibernate-configuration>

Michael,

something else. I turned on the 'show sql' property.
When i run the program i see the following

Hibernate: select materialfo0_.id as id0_, materialfo0_.Available as Available0_, materialfo0_.CreatedBy as CreatedBy0_, materialfo0_.CreatedOn as CreatedOn0_, materialfo0_.ModifiedBy as ModifiedBy0_, materialfo0_.ModifiedOn as ModifiedOn0_, materialfo0_.Mat_ID as Mat_ID0_ from MaterialForKit materialfo0_ where materialfo0_.Mat_ID=?

(and this about 20000 times)
Is this normal, since i turned on the use_outer_join?

If hibernate is doing a seperate select for every record he finds in the Material-table to get the MaterialForKit-object, then its normal it takes that long. Is there a way hibernate could get the information in one go???

Thanks

Frank

if you have A --* B
and want the graph to be loaded in one time call
select a from A a join FETCH a.bs

it will go faster now ;)

Hi Anthony and Michael,

i tried the join, added in the mapping-file for the material the "outer-join=true" and tried all sorts of things. Hibernate always needs 29 secs to retrieve the list and i keep seeing a lot of seperate selects for the retrieval of the materialforkit-records.

Is there something wrong with my cfg and mapping files????

please read about "lazy laoding" and just change


to

Hi Anthony,

at first i thought 'hey, it works!'. Then i noticed i only got back the material for which a materialforkit existed. So i changed the join fetch to left outer join fetch. Guess what? --> 29 secs.

I implemented the retrieval myself via standard jdbc and a resulset: it takes 4 seconds!

So, could somebody plse tell me what is going wrong here????

Thanks

Frank

This is the cuplrit:



Currently, Hibernate's result-set processor has problems handling optional one-to-one associations on a property-ref.

Its actually a really hard problem to handle this correctly.

Hi Gavin,

thanks for your answer.

Question(s):
- is there a work around in hibernate for this problem?
- if not, whats your suggestion to solve it
- are you planning to solve this problem in a forthcomming release?

Thanks

Frank