Does Batch-Fetching work with lazy=false?

etwcn · **Joined:** Thu Feb 08, 2007 10:40 am **Posts:** 46

Hi,

I have a one-to-many relation between products and parts, i.e. a product has many parts:

Code:

<class name="Product" lazy="false">
    <id name="serialNumber" column="productSerialNumber"/>
    <set name="parts" lazy="false"  batch-size="30">
        <key column="productSerialNumber" not-null="true"/>
        <one-to-many class="Part"/>
    </set>
</class>

Now say that I have 10000 products in the database and I retrieve all available products with the hql query "from Product". Hibernate issues a single sql query to the product table and then 10000 separate queries to the part table. This is quite slow.

Now, I was hoping to be able to reduce the number issued sql quries with batch-fetching. Hence, I added a batch-size="30" attribute. However, this does not have effect. Hibernate still generates 10001 sql queries.

Is this because of the non-lazy mapping of the collection? Does batch-fetching work at all, when using non-lazy collections?

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

Hi

Try fetch="subselect" in the set mapping

Still, batch fetching should work as expected

Can you post the code where you load it?

mmerder · **Posted:** Thu Jan 15, 2009 9:52 am

Even better, use fetch="join", so that only a single query is sent to the database.

To enable batch-fetching you have to enable it via hibernate-configuration (set hibernate.jdbc.batch_size and hibernate.default_batch_fetch_size).

You can do that in persistence.xml (if you are using JPA) or in hibernate.cfg.xml.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

If HQL is used, the fetch="join" from the mapping will be ignored

only get/load and criteria respect the mapping

HQL would have to be like "from Product p left join fetch p.parts"
but then you would have to cast the result-List to a set to eliminate double results
so one query for products and one for subselect of parts is easier in my opinion

mmerder · **Posted:** Thu Jan 15, 2009 10:29 am

pkleindl wrote:

If HQL is used, the fetch="join" from the mapping will be ignored

only get/load and criteria respect the mapping

Yes, you are totally right.

etwcn · **Joined:** Thu Feb 08, 2007 10:40 am **Posts:** 46

pkleindl wrote:

Hi

Try fetch="subselect" in the set mapping

Still, batch fetching should work as expected

Can you post the code where you load it?

I have now tried to use fetch="subselect". However, Hibernate still issues 10001 sql select statements.

Here is the code used to execute the query:

Code:

Session session = SessionFactoryManager.newSession();
List result;
try {
    session.beginTransaction();
    Query query = session.createQuery("from Product");
    result = query.list(); 
} finally {
    Transaction transaction = session.getTransaction();
    if ((transaction != null) && (transaction.isActive())) {
        transaction.rollback();
    }
    session.close();
}
// work with the result ...

Is there anything else I could try?

mmerder · **Posted:** Fri Jan 16, 2009 10:04 am

Did you also enable batch-fetching in your configuration, as i said in a posting before? In my tests, this worked.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

Yes that explains it.

If you close the session the objects become detached and Hibernate seems to loose the information how the list was loaded ("from Product").

So it has to iterate over every single id.

move the "// work with the result ... " part inside the session and it should work

do you open another session to load the parts? else you should get a LazyInitializationException there in any case...

etwcn · **Joined:** Thu Feb 08, 2007 10:40 am **Posts:** 46

As the topic title already says: I am not using lazy loading. In fact, I am on a 3-tier architecture, where the hibernate code is located within the server-tier and the "work with result" part is executed in the client. Thus, I cannot move that part inside the session.

Here the part of my configuration, which in my opinion should enable batch-fetching:

Code:

<property name="max_fetch_depth">2</property>
<property name="default_batch_fetch_size">16</property>
<property name="jdbc.fetch_size">210</property>
<property name="jdbc.batch_size">30</property>

What I am getting so far from this discussion is that batch fetching does not seem to work, if lazy loading is disabled.

mmerder · **Posted:** Mon Jan 19, 2009 6:19 am

The problem is not in the eager-fetch. There has to be something else in your mapping. Here is my equivalent test-case using annotations (which should not be the important point):

Code:

@Entity
public class Customer {
    ...
    @OneToMany(fetch=FetchType.EAGER)
    @JoinColumn(name="customer_id")
    public Set<Contact> getContacts() {
        return contacts;
    }
    ...
}

In my persistence.xml:

Code:

   <property name="hibernate.max_fetch_depth" value="3" />
   <property name="hibernate.default_batch_fetch_size" value="16" />
   <property name="hibernate.jdbc.batch_size" value="2" />

My test-statement:

Code:

em.createQuery("from Customer").getResultList();

And here is my generated SQL:

Quote:

Hibernate:
select
customer0_.id as id1_,
customer0_.someProperty as someProp2_1_,
customer0_.someProperty2 as someProp3_1_
from
Customer customer0_
Hibernate:
select
contacts0_.customer_id as customer3_1_,
contacts0_.id as id1_,
contacts0_.id as id0_0_,
contacts0_.someProperty as someProp2_0_0_
from
Contact contacts0_
where
contacts0_.customer_id in (
?, ?
)

As you can see, batch-fetching works with eager collections in this case, maybe you can find a difference to your case, besides using JPA and Annotations.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

etwcn wrote:

As the topic title already says: I am not using lazy loading. In fact, I am on a 3-tier architecture, where the hibernate code is located within the server-tier and the "work with result" part is executed in the client. Thus, I cannot move that part inside the session.

...
What I am getting so far from this discussion is that batch fetching does not seem to work, if lazy loading is disabled.

It works for us so the problem _might_ be in how you access the data.

Try the following please:
- remove lazy=false from class and set
- add fetch="subselect" to set
- try the following code _inside_ the session:

Code:

Query query = session.createQuery("from Product");
ArrayList<Product> result = (ArrayList<Product>)query.list();
if (result.size() > 0) {
  result.get(0).getParts().size(); // this should trigger loading all parts by subselect
}

Hope i typed it correctly