Monday, July 02, 2007

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.

7 comments:

  1. Anonymous1:03 PM

    Awesome. :) I was about to blame MySql and switch to using LIMIT and OFFSET or something silly like that.

    Thanks alot.

    ReplyDelete
  2. Sweet - glad this was helpful.

    ReplyDelete
  3. Anonymous2:12 PM

    I was pulling my hair out over a very large query I was using. After using your tip it's speed improved 10x. Thanks!

    ReplyDelete
  4. I'm so happy to hear this was useful!

    -Ben

    ReplyDelete
  5. Anonymous1:22 PM

    Depending on your fetch mode, the 'bug' might have resulted from hibernate handling pagination in memory. See this article: http://java.dzone.com/articles/hibernate-tuning-queries-using

    Perhaps an easier way to debug this problem would be to look at the hibernate logs rather than debugging the code.

    ReplyDelete
  6. it worked , thank u very much

    ReplyDelete
  7. pls help out Hi , i have the stored proc am Calling the Stroed Proc with the help of getNamedQuery mehod and am Able to get the result Directly but i want it in form pagination when use
    q.setFirstResult(2);
    q.setMaxResults(20);
    q.setFetchSize(2);
    it givving error like Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis i f i doesn't use the Above Thre when i use Direct q.list it will be Fine Wat ui have to Boss Can any one help me OUt

    ReplyDelete