Hibernate performance tip: setFetchSize

A good portion of my day was spent tracking down this gem of a bug. I had a query like the following:

  SELECT id FROM OrderedTestScores WHERE testId = ?

In Java, I was using Hibernate code like so:

  Query q = session.getNamedQuery("scoreQuery");
  q.setFirstResult(getOffset());
  q.setMaxResults(getResultsPerPage());
  return q.list();

I would run the query in Postgres' psql and it would run in a matter of milliseconds. From a Java app though, the query would quite often take 10 - 15 seconds (yes, seconds) to complete.

What gives?

An explain on the query showed that the number of rows returned was trivial. I vacuumed and analyzed the database till it shined. And no matter how hard I tried, I couldn't get psql to take more than a couple hundred milliseconds to run the query.

Finally, one of my team members suggested we bust out Beanshell and play with the Hibernate code interactively. Brilliant suggestion.

What we noticed was that the above code really was consistently slow. We simplified it by saying:

  Query q = session.getNamedQuery("scoreQuery");
  q.list();

And, what do you know, the query ran fast. After a bit more testing, we learned that setMaxResults was singly handedly slowing down the query.

I have no idea why this is. I traced the hibernate code for about 3 minutes before giving up. I decided I could live without knowing the details behind this one (though, if anyone can explain it - I'd love to hear it!).

Another team member suggested that we add in a call to setFetchSize based on the fact that it often accompanies setMaxResults in the examples he's seen. It turned out to be yet another great suggestion. A few seconds playing around in Beanshell and we had proof that setFetchSize was indeed the antidote to setMaxResults

The speedy code turned out to be:

  Query q = session.getNamedQuery("scoreQuery");
  q.setFirstResult(getOffset());
  q.setMaxResults(getResultsPerPage());
  q.setFetchSize(getResultsPerPage());
  return q.list();

While this is somewhat reasonable (you want your results pulled in sane chunks) - c'mon - 15 seconds to run a query that should take 200 milliseconds? How massively wrong could the fetchsize be to cause that? Remember, we are talking about results sets of like 3 or 4 rows.

Most bugs have a really simple cause and explanation. Except when they don't. It's what makes being a developer so much fun.