We are working on a new implementation and the project requires us to process bulk data. Some scenarios of data processing are mentioned below.
1) We have around 10-12 million records in the tables on which our processes run.
2) The select criteria to process a set of records fetches minimum 45-50K records. Some scenarios bring in upto 100K records.There is no way we can break up this based on the business requirement.

Our approach and issues in this approach we are facing are mentioned below
1) We are using Hibernate's Core Criteria to fetch the records. We get an outOfMemory/heap size exception when the number of records found are more than 25K inspite of setting the following property to increase the JVM heap size -XX:MaxPermSize=512M -Xms512m -Xmx512m
2) We have tried setting the following properties to set the hibernate.jdbc.batch_size as well but it doesnt resolve the issue :
<prop key="hibernate.jdbc.batch_size">20</prop>
3) We have tried to set the second level cache to false but of no use
<prop key="hibernate.cache.use_second_level_cache">false</prop>
4) We set the property to use scrollable result set but of no use
<prop key="hibernate.hibernate.jdbc.use_scrollable_resultset">false</prop>

I have following questions :
1) Is it really advisable to use Hibernate or even hql to handle such large volume of data or it is better to use the native SQL in such scenarios.
2) Does anyone have specific instances to quote who have handled/processed such large volumes of data using hibernate.
3) Do we have any suggestions/best practices/recommendations for handling such large volume of data.

You say you use the native Hibernate API.

When you query for those 50k+ rows, do you use Projections to limit the number of columns returned from the underlying table(s), or do you return all columns each time because you don't have a choice?

In actual, the table which we are trying to query has around 30 columns. Just to try this out, we mapped just 10 columns of the table to our entity object. This is the most simple case when we are trying to read data from just one table. Just to summarize,we just get the columns we need and not all the columns of the tables. The select criteria does not even involve a join to another table. Have you tried getting 50K+ records using hibernate?

After looking a little more at the API (and the Hibernate user guide), I've seen that you can limit the result set size by using two methods, valid both for the Criteria (or DetachedCriteria) API and for the Query API: setFirstResult() and setMaxResults(). Both take an integer as an argument. Beware, it starts at 0, not 1.

Also, you can use .iterate() in both cases as well, if you can afford to iterate over the results.

I was always told to avoid Hibernate, i.e., use direct JDBC API, for that many number of records (more than 100,000).

Am also aware of something called StatelessSession, which I believe gives you better performance in case you have used Hibernate AND that you will not bring or deal with collections and relationships and then there is batching, which has to be used by you, I believe, to avoid dealing with all the records at the same time and hence avoid memory issues!

I will update you more when I use the StatelessSession and you do keep posting your solutions even if you don't come across better ideas!

Regards
Krishna

Hibernate_reference Ch13. Batch processing has it all!!!

All the best.

Regards
Krishna

Except that, apparently, you can't use StatelessSessions with classes that contain a Collection member that is mapped with lazy="false".

I believe that to be the case, although I haven't yet confirmed it. I have an open topic in this forum on that subject: https://forum.hibernate.org/viewtopic.php?f=1&t=998373. If anyone knows the way around this, I'd love to hear it either here or there.

:) Already faced this issue while testing, here is the patch discussed at
https://forum.hibernate.org/viewtopic.php?f=1&t=957223

the patch is at
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3220

Interesting. In your case you could still make your StatelessSession query involving an object with lazy=false, ultimately resulting in a threading issue, whereas in my case, a much simpler query, it immediately refused to make the query giving the exception I mentioned in my thread.

In my case the solution was simple: to remove the lazy=false and rewrite the three dao methods that ever fetched this object to dynamically fetch eagerly (setFetchMode or access the collection).

I begin to believe that lazy=false is actually the "lazy man's way to do eager fetching" and should be avoided if at all possible.

Ha haa :), exactly, the same thing was ringing in my mind, it is never too late!

Regards
Krishna