Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.0.3

Mapping documents:
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory name="DETECTION_ENGINE_TRADE_SURVEILLIANCE">
<property name="show_sql">false</property>

<property name="hibernate.dialect">org.hibernate.dialect.DB2Dialect</property>
<property name="hibernate.connection.driver_class">com.ibm.db2.jcc.DB2Driver</property>
<property name="hibernate.connection.url"></property>
<property name="hibernate.connection.username"></property>
<property name="hibernate.connection.password"></property>
<property name="hibernate.default_schema"></property>

<property name="hibernate.jdbc.fetch_size">2</property>
<property name="hibernate.default_batch_fetch_size">2</property>

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.min_size">0</property>

<mapping resource="com/gs/fw/gc/ds/das/db/orm/map/Trade.hbm.xml"/>
<mapping resource="com/gs/fw/gc/ds/das/db/orm/map/Product.hbm.xml"/>
<mapping resource="com/gs/fw/gc/ds/das/db/orm/map/Account.hbm.xml"/>
</session-factory>
</hibernate-configuration>



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

<hibernate-mapping schema="amlusprod" default-lazy="false">

<class name="com.gs.fw.cmpl.de.data.Account" table="Account_View" lazy="false" batch-size="10">
<cache usage="read-only"/>
<id name="accountId" type="string" unsaved-value="null" >
<column name="sub_acct_c" not-null="true"/>
</id>

<set name="productList" inverse="true" lazy="false" order-by="sub_acct_c ASC, prime_id_c ASC">
<cache usage="read-only"/>
<key column="sub_acct_c"/>
<one-to-many class="com.gs.fw.cmpl.de.data.Product" />
</set>
</class>

<query name="DE_ACCTS_WITH_TRADES">
<![CDATA[
from Account order by accountId
]]>
</query>

</hibernate-mapping>


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

<hibernate-mapping schema="amlusprod" default-lazy="false">

<class name="com.gs.fw.cmpl.de.data.Product" table="Product_View" lazy="false">
<cache usage="read-only"/>

<id name="productId" type="string" unsaved-value="null" >
<column name="prime_id_c" not-null="true"/>
</id>

<set name="tradeList" inverse="true" lazy="false" order-by="prime_id_c ASC">
<cache usage="read-only"/>
<key column="prime_id_c"/>
<one-to-many class="com.gs.fw.cmpl.de.data.Trade" />
</set>


</class>

</hibernate-mapping>

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

<hibernate-mapping schema="amlusprod" default-lazy="false">

<class name="com.gs.fw.cmpl.de.data.Trade" table="Trade_View" lazy="false">
<cache usage="read-only"/>
<composite-id>
<key-property name="tradeDate" column="trade_date_d" type="date"/>
<key-property name="tranValue" column="post_amt_c" type="double"/>
<key-property name="tranQty" column="sec_qty_c" type="double"/>
</composite-id>

</class>

</hibernate-mapping>





Code between sessionFactory.openSession() and session.close():
Iterator<Account> iter = (session.getNamedQuery(_namedQuery)).iterate();
end = System.currentTimeMillis();
duration = end - start;
System.out.println("HIBERNATE SELECT NQ DURATION: "+duration);

List<Account> acctList = new ArrayList<Account>();
start = System.currentTimeMillis();
while ((iter != null) && iter.hasNext())
{
startrow = System.currentTimeMillis();
acct = iter.next();

acctList.add(acct);
end = System.currentTimeMillis();
duration = end - startrow;
System.out.println("HIBERNATE NEXT: "+duration);
}



Full stack trace of any exception that occurs:

Name and version of the database you are using:
DB2 UDB 8.x

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

General Question about how I can improve performance:

I have the object graph:

Account with many products and products with many trades....

I thought if I set the fetch size it would load that number of the parent objects plus all its children??
I would like to use an iterator, because there can be over 1 million rows. I saw in the documentation:

11.4.1.1. Iterating results
Occasionally, you might be able to achieve better performance by executing the query using the iterate() method. This will only usually be the case if you expect that the actual entity instances returned by the query will already be in the session or second-level cache. If they are not already cached, iterate() will be slower than list() and might require many database hits for a simple query, usually 1 for the initial select which only returns identifiers, and n additional selects to initialize the actual instances.

QUESTION:

1. What can I set, so Hibernate can fetch 10 rows and initialize 10 (or more) instances at a time??

Hi,

The fetch size is not related to the row count. It specifies the depth of one-to-one and one-to-many association that hibernate will hydrate.

If you set max_fetch_depth to 0 and load an account, only the account will be loaded. If you set it to 1 and query on an account, the account and its associated Product(s) will be loaded. If you set it to 3, then trades will be loaded as well on each product associated to the account.

I think what you are looking for is pagination.



You could set up a method like this one


Let me know if this helps.

Vincent

Thanks!! I will give it a try and let you know..

Kenny

Vincent,

Is there any way to tell hibernate to LOAD and create the INSTANCES??

For example if I set the row count to be 5, I would like for hibernate to load 5 Instances of Account (with the instances of its children and grand children also loaded)??

In this case it does not have to make 5 calls to the database, only 1.

Thanks,

Kenny

Hi,

As far as making a single query for multiple objects, I have never tried it, but there is a batch-size attribute on the <class> element.

It reads:

Concerning the child objects: children and grandchildren to be loaded as well, you need to put lazy=false on their mapping to make sure that they are loaded and there is a fetch property on the <map> and <set> elements that you can use:



As it says, only one collection can be done through an outer-join. In your case you should be fine.

Let me know if it worked.