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));
Hibernate: