When recieving large resultsets using criteria.list() or query.list() Hibernate is really slow. In my case the resultset is about 12 000 rows. The same query takes 120ms to execute directly against the DB but takes >40 sec to excecute through Hibernate. I must do something seriously wrong or is it not possible to recieve large resultsets as list in Hibernate?

What database? Any network between Hibernate and the database? How large are your objects? Do you see GC-calls or heavy CPU-activity during the request? How do you execute the SQL directly? Do you spool the resultset or do you measure just the time of the statement?

DB: Oracle 11g
Yes the database is on another server. But in our production env the app-server and db-server are on the same gbit LAN and still about the same (about 30sek).
The object mapped by the query has got about 20 properties (String, Integer, Double, Date) and corresponding get/set-methods.Each row in db table is 204bytes.
No GC during execution.
A moderate increase in CPU-activity during processing.
When testing directly I use Oracle SQL Developer, quess it measures the time to execute the statement.

Did you find the solution for this problem?
I face the same issue. Any solution is really appreciated.

Hi abisheikfoi,

Performance really hits bad when its about thousands of rows. The jinx here is that there is a full row scan on each where condition. Check the query stats and post it here. Try to form the query using index scan. Dont have all the properties included in the mapping document. It would be answerable if you have the query, mapping document & the query stats.

It is possible to handle large result sets in Hibernate. I regularly handle queries that return 100000+ entities. Hare are a few things that that you can try/check:

* Use Query.iterate() instead of Query.list(). This keeps the memory usage down if combined with regular calls to Session.clear() or Session.evict().
* Make sure that the JDBC driver knows how to handle large result sets. I use MySQL myself and the default setting in the JDBC driver is to download the entire result set in one go. This uses far too much memory. Don't know anything about Oracle...
* Make sure that you don't have any non-lazy associations or collections. This may result in additional queries for each result in the original result set. If you need to navigate an association later in the code, performance can be increased a lot by including a "fetch join" in the first query.

There are probably other things to do as well, but the three points above have all "bit" me in the past.

use the following code for paging
do you need all data in 1 time???

Yes but it also does not account for transferring the results across the network to the client and doing things with the result set values. Granted, comparing Hibernate to straight-up JDBC access is like comparing apples and oranges, but here its more like comparing apples to cars.