Hibernate version: 2.1.1


Hi all

The project I am working needs to minimises the number of SQL queries to genereted to the database since the performance of the program will be serious degraded if more sql get genereted to the database.

I have a product table that has one-to-many relationship with the color table. When I make a query to retrieve 20 records from the product, it will generate 1 query for getting the 20 products records but another 20 sqls for the color table of each product. This makes my program significantly slower and I would like to know how to avoid this with hibernate?


I also have some others that have a few one-to-many relationships, therefore, will generate a few sql for each record I want to retrieve. Please help!



Thank you very much!


Best regards


Edmund

Try



in your collection mapping.

HTH
Ernst

... except that you can't use the fetch attribute in Hibernate 2.x because it doesn't exist. Try the outer-join attribute instead.

I have tried to use outer-fetch but it is still generating one sql for each one-to-many relationship. I am wondering if I haven't used the outer-fetch properties correctly. I am MS-SQL 2000.

I have added the following line in the hibernate.properties

hibernate.max_fetch_depth=2


The xml file:

<bag
name="productPackagings"
lazy="true"
inverse="true"
cascade="delete"
outer-join="true"
>
<key>
<column name="product_id" />
</key>
<one-to-many
class="erp.dataaccesslayer.hibernate.dataobjects.ProductPackaging"
/>
</bag>


I have tried to put the outer-join="true" in the <one-to-many> like the following:


<bag
name="productPackagings"
lazy="true"
inverse="true"
cascade="delete"
>
<key>
<column name="product_id" />
</key>
<one-to-many
class="erp.dataaccesslayer.hibernate.dataobjects.ProductPackaging" outer-join="true"
/>
</bag>


But I have exception when the Hibernate initialises telling there is no such properties defined.



Thanks a lot!


Best regards

Edmond