How to handle large dataset with JPA (or at least with Hibernate)?
Posted
by Roman
on Stack Overflow
See other posts from Stack Overflow
or by Roman
Published on 2010-05-03T22:08:22Z
Indexed on
2010/05/03
22:48 UTC
Read the original article
Hit count: 297
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)?
© Stack Overflow or respective owner