java - Why is the Hibernate query.list() slow?

Question

I am using Hibernate 4.1.6 and having issues with the speed that a list is built. I am running the following query.

public void doQuery(final Baz baz){
  final Query query = getSessionFactory().getCurrentSession().createQuery(
          "select c.id, foo.someValue from Foo as foo "+
          "join foo.a as a"+
          "join foo.b as b "+
          "join b.c as c "+
          "where baz=:baz"
          );
  query.setParameter("baz", baz);
  Long start=System.currentTimeMillis();
  final List<Object[]> list = query.list();
  Long end=System.currentTimeMillis();
  System.out.println((end-start));
}

I set hibernate debugging on to get the actual query that is sent to the database. I ran that query directly in the database and it returned 23,000 rows in 0.015 ms. So, I'm guessing the query is not the issue. The example above shows it takes ~ 32 seconds to create that list. Is there something that can be done to speed that up?

Update: I tried using the createSQLQuery() method using the hibernate debugging query and it ran just as slow as the createQuery() method.

Update: I tried using a stateless session but it ran just as slow.

Update: I outputted some statistics (setting the hibernate.generate_statistics flag to true) but nothing looks alarming to me:

Hibernate SessionFactory Statistics [
    Number of connection requests[4]
    Number of flushes done on the session (either by client code or by hibernate[3]
    The number of completed transactions (failed and successful).[3]
    The number of transactions completed without failure[3]
    The number of sessions your code has opened.[4]
    The number of sessions your code has closed.[3]
    Total number of queries executed.[4]
    Time of the slowest query executed.[28258]
    the number of collections fetched from the DB.[6]
    The number of collections loaded from the DB.[6]
    The number of collections that were rebuilt[0]
    The number of collections that were 'deleted' batch.[0]
    The number of collections that were updated batch.[0]
    The number of your objects deleted.[0]
    The number of your objects fetched.[1]
    The number of your objects actually loaded (fully populated).[204]
    The number of your objects inserted.[1]
    The number of your object updated.[0]
]

Hibernate SessionFactory Query Statistics [
    total hits on cache by this query[0]
    total misses on cache by this query[0]
    total number of objects put into cache by this query execution[0]
    Number of times this query has been invoked[1]
    average time for invoking this query.[28258]
    maximum time incurred by query execution[28258]
    minimum time incurred by query execution[28258]
    Number of rows returned over all invocations of this query[23303]
]

Update: I see the same slowness when doing a next() from a ScrollableResults from a native query. Note that I am doing nothing in the loop.

    ScrollableResults results = query.scroll();
    Long start=System.currentTimeMillis();
    while (results.next()) {
       //do nothing
    }
    Long end=System.currentTimeMillis();
    System.out.println((end-start));

Answer 1

I am not 100% certain on this answer, since tuning / optimization issues are always so difficult to pinpoint.

However, based on the fact that you turned on show_sql, extracted the query, and ran it directly against the database and saw sub-second results vs execution time through Hibernate Query, I am focusing on the manner in which Hibernate is constructing and hydrating objects that result from the query.list() call.

Here is another user who mentioned similar Query performance issues in Hibernate, and saw dramatic performance increases by adding full convenience constructors (constructors that accept a value for each field) in the POJO: Simple hibernate query returning very slowly

It sounds like they stumbled upon this fix, and there was not a clear understanding of why this worked. There was speculation regarding Hibernate having to use reflection to detect properties. I am curious myself and am planning to dig into the source code for Hibernate to better understand this when I have a chance. In the meantime, though, you may wish to look into adding these full constructors with parameters for all of your POJO class attributes and see if that makes a difference.

Please do let me know what you find, as I am very interested in Hibernate performance optimization. Thanks!