Reduce N+1 Selects in Hibernate with Batch Fetching

The problem: one query per row

You deploy a feature that displays a list of customer orders. Each order has a lazy collection of line items. When the view touches order.getItems(), Hibernate issues one SELECT for the first order, then another for the second, and so on. With 50 orders, that is 50 extra round trips. This is the classic N+1 select problem, and it often stays hidden in development because the dataset is small.

What batch fetching changes

Hibernate ORM supports batch fetching through the @BatchSize annotation and the hibernate.default_batch_fetch_size property. When a lazy association is accessed, Hibernate can load multiple related rows in a single query using an IN clause instead of issuing one select per row. The default batch size is 1, which means no batching happens unless you configure it.

Configuration options

You can set a global default in your persistence configuration:

hibernate.default_batch_fetch_size=25

Or target a specific association:

@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@BatchSize(size = 25)
private Set<OrderItem> items;

Annotation-level @BatchSize overrides the global default for that attribute. Batch fetching works with FetchType.LAZY and SELECT fetching. It is not the same as join fetching; if you already need the entire object graph upfront, an entity graph or JOIN FETCH is usually more direct.

Worked example: tuning an order listing

Suppose your service loads orders and the template iterates over their items.

List<Order> orders = entityManager.createQuery(
    "select o from Order o", Order.class)
    .getResultList();

for (Order order : orders) {
    // Without batch fetching: one SELECT per order here
    for (OrderItem item : order.getItems()) {
        total += item.getPrice();
    }
}

Add batch fetching to the items collection and enable SQL logging so you can observe the change:

hibernate.show_sql=true
hibernate.format_sql=true
hibernate.default_batch_fetch_size=25

Run this in your application, not in a production database shell. You need only file-system permissions to edit the configuration and restart the application. Before changing anything, capture the SQL log for a representative page load and count the SELECT statements against the line-item table. After adding @BatchSize(size = 25) or the global property, repeat the same load and compare the count. If Hibernate issues one SELECT ... WHERE order_id IN (...) per batch instead of one per order, round trips have dropped.

Do not assume the development result will match production. A batch size that looks safe with 10 orders can over-fetch with 1,000, and very large IN lists can exceed database limits or slow down query planning.

Limitations and when not to use it

• Over-fetching: A batch size of 100 loads up to 100 related rows even if the application only touches one. That wastes memory and database bandwidth.
• Not a join substitute: Batch fetching still issues separate queries. If you need orders and items together immediately, use an entity graph or JOIN FETCH.
• Session boundaries: Accessing a lazy proxy after the session or transaction closes still triggers individual selects, regardless of batch size.
• Pagination: Combining batch fetching with eager association fetching on paginated queries can duplicate rows or change result-set cardinality.
• Version differences: Hibernate 5 and Hibernate 6 handle batch fetching differently. Check your ORM version before tuning.

Practical check and next steps

Start with hibernate.default_batch_fetch_size=25 for a single high-traffic association. Enable SQL logging in a staging environment, run a realistic scenario, and count round trips before and after. If the association is sparse and most rows are never used, lower the size or remove batch fetching for that field. Verify with production-like data because batch size tuning is data-dependent.

There is no database migration or schema change to roll back; if the setting causes problems, remove the annotation or lower the property and restart.