Hi everyone -
I have a question not about a bug but about performance issues and your opinions on the best mapping strategy.

I'm using Hibernate version 2.1.6 with Spring 1.1.3, and have the following mapping.

<hibernate-mapping>
<class name="commerce.catalog.product.ProductGroup"
table="Product_Group" dynamic-update="false" dynamic-insert="false">

<id name="id" column="pg_id" type="java.lang.Integer" unsaved-value="null">
<generator class="increment"/>
</id>

<set name="productSKUs" lazy="false" inverse="false" cascade="all" sort="unsorted">

<key column="pg_id"/>

<one-to-many class="commerce.catalog.product.ProductSKU"/>
</set>

<map name="properties" table="product_group_properties" lazy="true" sort="unsorted" inverse="false" cascade="all" >
<key column="product_group_id" />
<index column="PROPERTY_NAME" type="java.lang.String"/>
<element column="PROPERTY_VALUE" type="java.lang.String" />
</map>

</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="commerce.catalog.product.ProductSKU" table="Product_Sku" dynamic-update="false" dynamic-insert="false">

<id name="id" column="sku_id" type="java.lang.Long" unsaved-value="null">
<generator class="increment"/>
</id>

<map name="properties" table="product_sku_properties" lazy="true" sort="unsorted" inverse="false" cascade="all" >
<key column="product_sku_id" />
<index column="PROPERTY_NAME" type="java.lang.String"/>
<element column="PROPERTY_VALUE" type="java.lang.String"/>
</map>
</class>
</hibernate-mapping>

As you can see, I have an object called ProductGroup which has a set of ProductSKUs. In addition both ProductGroup and ProductSKU have an association to a "properties" table.

The issue I'm trying to resolve is performance related and comes from when I try to do the following query.

from ProductGroup pg where pg.id in (:idList)

With a large enough set of ProductGroups (each with a large enough set of ProductSKUs), I get approximately 10,000 queries being executed before this query returns. The basic steps of execution to finish are as follows:

It runs the following query once:
select productgro0_.pg_id as pg_id, decode (productgro0_.pg_id, productgro0__1_.nw_product_group_id, 1,0 ) as clazz_, productgro0_.name as name48_, productgro0_.code1 as code148_, productgro0_.code2 as code248_, productgro0_.list_price as list_price48_, productgro0_.long_description as long_des6_48_, productgro0_.short_description as short_de7_48_, productgro0_.sale_string as sale_str8_48_, productgro0_.display_rank as display_9_48_, productgro0_.visible as visible48_, productgro0_.creation_date as creatio11_48_, productgro0_.modified_date as modifie12_48_, productgro0_.catalog_id as catalog_id48_ from Product_Group productgro0_, nw_product_group productgro0__1_ where productgro0_.pg_id=productgro0__1_.nw_product_group_id(+) and ((productgro0_.pg_id in(? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)))

Then for each ProductGroup in the result set, it runs the following queries:
select properties0_.PROPERTY_VALUE as PROPERTY2___, properties0_.product_group_id as product_1___, properties0_.PROPERTY_NAME as PROPERTY3___ from product_group_properties properties0_ where properties0_.product_group_id=?
select productsku0_.sku_id as sku_id__, productsku0_.pg_id as pg_id__, productsku0_.sku_id as sku_id0_, decode (productsku0_.sku_id, productsku0__1_.nw_product_sku_id, 1,0 ) as clazz_0_, productsku0_.backorder_quantity as backorde2_50_0_, productsku0_.code1 as code150_0_, productsku0_.expected_date as expected4_50_0_, productsku0_.list_price as list_price50_0_, productsku0_.name as name50_0_, productsku0_.quantity_on_hand as quantity7_50_0_, productsku0_.sell_price as sell_price50_0_, productsku0_.sku as sku50_0_ from Product_Sku productsku0_, nw_product_sku productsku0__1_ where productsku0_.pg_id=? and productsku0_.sku_id=productsku0__1_.nw_product_sku_id(+)

Then for each SKU in the above result set it runs the following query:
select properties0_.PROPERTY_VALUE as PROPERTY2___, properties0_.product_sku_id as product_1___, properties0_.PROPERTY_NAME as PROPERTY3___ from product_sku_properties properties0_ where properties0_.product_sku_id=?

So when I want to retrieve all the ProductGroups from a set of say 20 IDs, and each of those 20 ProductGroups has 50 SKUs, thenI'm running 1,000 queries to retrieve all these ProductGroups and their associated Collections. Basically I'm trying to make sure I can add any additional fields to the property table without having to remap the entire ProductGroup or ProductSKU objects.

Before I start working on caching or anything, I'd like to see if anyone has a better way of mapping this type of relationship out.

I'm trying to reduce that number of queries and was wondering what experiences everyone out there had in terms of optimizing Hibernate performance.

I'm running on Oracle 10 and JBoss 3.2.6.

Thanks for any input or advice.

S.Park