Query Tuning with HQL and Criteria: A Field Guide

Posted on By Luis Fernandez

Dialogue

Dev: My page takes five seconds. The query looks innocent.

DBA: Your logs show 412 selects. Nothing innocent there.

Dev: But I am using Hibernate with HQL and Criteria. It is supposed to help.

DBA: Tools help if you aim them. Right now you are spraying the database.

If that chat sounds familiar, keep reading. With everyone shipping apps on Hibernate 3 and JPA 1.0, we are all juggling lazy loading, caching, and SQL that we did not hand craft. The trick is learning how to steer it.

Evidence

Let me show what I have been seeing this week while tuning a Spring plus Hibernate stack talking to MySQL 5 and Oracle 10g. Same patterns keep popping up.

  • N+1 selects when walking associations in a loop. One query for parents, then one per child collection.
  • Pagination mixed with join fetch that explodes rows and ruins the page query.
  • Criteria that joins where we only need IDs, because we forgot to use Projections.
  • Caching switched on with no thought about the shape of keys or eviction, which turns into stale reads.

Here is a real slice from the logs. One page render, 50 orders with their last two items:

// HQL
List<Order> orders = session.createQuery(
  "select o from Order o order by o.created desc"
)
.setMaxResults(50)
.list();

for (Order o : orders) {
  // Needs last 2 items
  List<Item> items = o.getItems(); // LAZY
  int size = Math.min(items.size(), 2);
  for (int i = 0; i < size; i++) {
    render(items.get(i));
  }
}

Hibernate happily fires one query for orders, then up to 50 more for items. That is the classic N+1 pattern. Time goes away fast when round trips pile up.

Now look at Criteria, same shape different paint:

Criteria c = session.createCriteria(Order.class)
  .addOrder(Order.desc("created"))
  .setMaxResults(50);

List<Order> orders = c.list(); // Items still lazy

Criteria does not magically fix it. We still need to ask for the right data in one go or in sane batches.

Notes from the trenches

1. Kill N+1 with fetch join for read pages

// HQL with fetch join
List<Order> orders = session.createQuery(
  "select distinct o from Order o " +
  "left join fetch o.items i " +
  "where o.status = :s " +
  "order by o.created desc"
)
.setParameter("s", Status.OPEN)
.setMaxResults(50) // see risk notes below
.list();

Use join fetch when you will touch the collection on the page. Add distinct to counter row multiplication.

2. Or batch the collection loads

// Mapping
<set name="items" batch-size="20"> ... </set>

// Or annotation
@BatchSize(size = 20)

Batch size tells Hibernate to load child collections for several parents in one query per batch. It keeps the select count down without changing your HQL.

3. Use projections when you only need a slice

// Criteria projection for summary rows
Criteria c = session.createCriteria(Order.class)
  .setProjection(Projections.projectionList()
    .add(Projections.property("id"))
    .add(Projections.property("created"))
    .add(Projections.property("total")))
  .addOrder(Order.desc("created"))
  .setFirstResult(0)
  .setMaxResults(50);

List<Object[]> rows = c.list();

Pull only the columns your table needs to show. You save memory and time on hydration. In HQL you can also use select new to map directly to a DTO.

// HQL DTO projection
List<OrderSummary> xs = session.createQuery(
  "select new com.acme.OrderSummary(o.id, o.created, o.total) " +
  "from Order o order by o.created desc"
).setMaxResults(50).list();

4. Control fetch mode in Criteria

Criteria c = session.createCriteria(Order.class)
  .setFetchMode("items", FetchMode.JOIN) // same as join fetch
  .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

5. Paginate with care

// Always pair both
query.setFirstResult(page * size);
query.setMaxResults(size);

Page at the database. Pair both bounds so the driver does not read silly amounts under the hood.

6. Index for your filters and sorts

-- MySQL
create index idx_order_status_created on orders(status, created desc);

-- Oracle
create index idx_order_status_created on orders(status, created);

Match the where fields first. If you sort often by created, include that column too. It pays back quickly.

7. Second level cache and query cache

// hibernate.cfg.xml
<property name="cache.use_second_level_cache">true</property>
<property name="cache.use_query_cache">true</property>
<property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>

// Java
Query q = session.createQuery("from Country order by name");
q.setCacheable(true);

Cache reference data like countries and states. Be picky with query cache. Only turn it on for stable lists or small filters. Pair it with clear eviction rules on writes.

Risks

  • Join fetch with paging: many databases page after join. A join that multiplies rows can kick out legit parents. Page on a subquery of IDs first, then join to hydrate.
  • Distinct is not free: it can force sorting or temp storage. Use it only when the join inflates rows.
  • Query cache surprises: it keys by SQL plus parameters plus the session factory state. One var in the HQL and you blow the region. Also watch invalidation storms on write heavy tables.
  • Count queries: count with joins can over count. Write select count(distinct o.id) or count a subquery of IDs.
  • Lazy loading in views: JSP tag calls that touch collections can open the fire hose. Either fetch before you hit the view or use Open Session in View with a clear contract on what the view can touch.
  • Driver fetch size: set hibernate.jdbc.fetch_size for large reads. Default can be tiny and cause many trips.

Graceful exit

We are shipping apps on Spring, Hibernate, and friends while the browser wars get spicy again and everyone is playing with Ajax. The database still calls the shots. If your page is slow, start by counting queries and rows, then shape the plan with smart HQL or Criteria. Small habits beat heroic rewrites.

Quick checklist

  • Turn on SQL logging and row count in dev.
  • Spot N+1 and fix with join fetch or batch size.
  • Project to DTOs for lists.
  • Set both bounds when paging.
  • Index by filter and sort columns.
  • Cache only stable stuff. Be explicit.

If you have a gnarly case, send me the HQL and the plan. I will happily trade war stories. Meanwhile, aim the tool. Let Hibernate do the lifting you asked for, not the lifting you forgot you asked for.

General Software Software Engineering