How to handle large dataset with JPA (or at least with Hibernate)?
- by Roman
I need to make my web-app work with really huge datasets. At the moment I get either OutOfMemoryException or output which is being generated 1-2 minutes.
Let's put it simple and suppose that we have 2 tables in DB: Worker and WorkLog with about 1000 rows in the first one and 10 000 000 rows in the second one. Latter table has several fields including 'workerId' and 'hoursWorked' fields among others. What we need is:
count total hours worked by each user;
list of work periods for each user.
The most straightforward approach (IMO) for each task in plain SQL is:
1)
select Worker.name, sum(hoursWorked) from Worker, WorkLog
where Worker.id = WorkLog.workerId
group by Worker.name;
//results of this query should be transformed to Multimap<Worker, Long>
2)
select Worker.name, WorkLog.start, WorkLog.hoursWorked from Worker, WorkLog
where Worker.id = WorkLog.workerId;
//results of this query should be transformed to Multimap<Worker, Period>
//if it was JDBC then it would be vitally
//to set resultSet.setFetchSize (someSmallNumber), ~100
So, I have two questions:
how to implement each of my approaches with JPA (or at least with Hibernate);
how would you handle this problem (with JPA or Hibernate of course)?
