Efficient way to process a large table

bcalmac · **Posted:** Fri Jun 18, 2004 5:53 pm

Hi guys,

I need to iterate over the records of a large table (a couple mil records) and I'm trying to figure out an efficient way to do it in hibernate.

1. using query.list() brings everything in memory (not scalable)
2. using query.iterate() or query.scroll() performs N + 1 queries (inefficient)

I was looking for some functionality similar to navigating a JDBC result set which reads data as you advance.

I terms of performance I would assume there is a big difference between using query.iterate() and JDBC resultset navigation.

How would you guys approach this issue?

Thanks,
Bogdan Calmac.

decairn · **Joined:** Tue Feb 10, 2004 2:30 pm **Posts:** 25

That many rows is troublesome, I'd likely drop to a stored procedure to execute it on the server.

bcalmac · **Posted:** Sat Jun 19, 2004 9:59 am

Well, the actual processing is provided by a different layer. I only iterate through the data and pass it to processing through an interface.

I guess I have to fallback to JDBC. But that means that I'm gonna have to populate the POJOs myself.

lneelaka · **Posted:** Sat Jun 19, 2004 10:55 am

Does this help?

http://forum.hibernate.org/viewtopic.php?t=930257

bcalmac · **Posted:** Sat Jun 19, 2004 10:59 am

Yes I saw that, Gavin said that scroll() should be implemented to fetch all the data rather than the IDs, but it's not currently implemented. On JIRA there is no indication for a plan to implement it.

Thanks,

Bogdan.

christian · **Posted:** Sat Jun 19, 2004 11:13 am

It is one of the minor features that was proposed for Hibernate3.

hypernate · **Posted:** Thu Jul 08, 2004 12:55 pm

Christian, this is an important feature for our application -- and any application that does large batch processing, I would imagine. Is there any official way to vote/voice support for v3 features? Or is that what I just did? ;)

christian · **Posted:** Thu Jul 08, 2004 12:58 pm

I think this has already been implemented, or at least, made its way on the TODO list. We will open a new JIRA project for Hibernate3 soon.

gavin · **Posted:** Fri Jul 09, 2004 12:39 am

It is not yet implemented, and probably won't make it into 30 alpha. I hope it will be there for 3.0 beta 1.

steve · **Posted:** Fri Jul 23, 2004 1:49 am

Just to round out this discussion, this has been implemented both in versions 2 and 3...

hypernate · **Posted:** Fri Jul 23, 2004 4:53 am

So this will be included in a future release on the 2.1.x codeline, hopefully 2.1.5?

christian · **Posted:** Fri Jul 23, 2004 5:36 am

Right

hypernate · **Posted:** Fri Jul 23, 2004 11:39 am

What can I say... you guys rock!

srosner · **Posted:** Wed Jul 13, 2005 3:46 am

Howdy,

I came across this thread, cause we found out as well that Hibernate 2.1.7 with DB2 produces a somewhat slow query using "rownumber()" in DB2-SQL when you use setMaxResults(n).

Using scroll() to obtain a ScrollableResults however did not do it for us, like discussed previously in this thread. The query (the invocation of query.scroll()) would roughly take the same amount of time like when we used setMaxResults(n) and invoked query.list().

Using the following code fragment did the job for us:

Code:

            Iterator it = query.iterate();
            int i = 0;
            result = new ArrayList();
            while ((i < maxResults) && (it.hasNext()))
            {
               i++;
               TransferObject to = (TransferObject) it.next();
               result.add(to);
            }

Maybe this helps someone!

Cheers,
Stephan

nimo23 · **Posted:** Tue Jan 13, 2009 8:29 am

double post