HQL Query over Bag Generating 57 SQL Selects?

rjhiii2002 · **Joined:** Fri Jun 02, 2006 9:55 am **Posts:** 2

HQL:
select itn from ItineraryDTO itn, itn.binderList bnd where bnd.binderId = ?

Mapping Doc element:
<bag name="binderList" table="Binder" lazy="false">

This HQL may be generating a large number of SQL statements. Is this poorly formed HQL (no join) or is the Bag structure causing the problem? I'll move this from DTO to VO but I need to understand if and why Hibernate would generate 57 SQL selects for the single HQL select above.

Thanks in advance for any Hibernate expertise. As an architect I see this as a recurring theme - trying to reuse Web service DTOs as value objects. WS standards dictate arrays for collections forcing Bags in the Hibernate mappings, which are defined with worst case performance.

This was just detected in a prod env and no more info is presently available - I'm looking for a heads up from someone with strong Hibernate experience to bring to the table.

Replying with additional Info:
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<bag name="binderList" table="Binder" lazy="false">
<key column="ITIN_ID"/>
<one-to-many class="BinderDTO"/>
</bag>

tads · **Posted:** Fri Jun 02, 2006 10:43 am

Hi,

When use lazy=false, the collection is aways loaded by Hibernate,
causing generation of sql.
I did the use of proxy and lazy=true. In the mapped class I put
proxy tag to load this only if the user want:

Code:

<hibernate-mapping>
    <class name="manager.Company" table="empresa"
       proxy="manager.Company"

.
.
.
.
        <bag name="companyAccounts" inverse="true" lazy="true">
         <key column="id_company"/>
         <one-to-many class=".manager.CompanyAccount"/>
        </bag>
.
.
.

but, if you want the company accounts, u need to use fetch on the HQL:

Code:

from Company c";
left join fetch c.companyAccounts ca";

and Hibernate.initialize when You use the session.load or session.get:

Code:

            company = (Company) session.load(Company.class, idCompany,
                    session, LockMode.UPGRADE);

            Hibernate.initialize(company);

// loading the list of company accounts
            Hibernate.initialize(company.getCompanyAccounts());

rjhiii2002 · **Joined:** Fri Jun 02, 2006 9:55 am **Posts:** 2

Thanks! A local guru confirmed a select touching the object with lazy false will cause the entire structure to be read in by generating a large number of SQL calls. The nested Bags all with lazy set to false went on for some time and was quite inefficient to retrieve a simple top-level ID.

Other selects used native SQL and shouldn't cause further query generation.

Thanks again:)
Bob

ramnath · **Posted:** Fri Jun 02, 2006 1:20 pm

Hi,

Th above poster is right.

When you have a list in the Class with relation. Hibernate loads the list of associated objects(one to many) pertaining to that Class. So set the lazy="true". Since hibernate set's lazy="false" if you have not set the lazy attribute in the mapping.

So rest of the 56 query might the queries to load the list of object.

All the best.