Pagination in SQL Server

jamunt · **Joined:** Mon Dec 11, 2006 5:30 am **Posts:** 8

Hi

I am using SQL Server 2K5 with hibernate. I am fetching large number of records in my hibernate around 1 million.

For fetching the records we are using setFirstResult and setMaxResult approach as shown below

int firstResult = i * fetchSize;
int fetchOnce = 1000;
do
{
query.setCacheMode(CacheMode.IGNORE).setFlushMode(FlushMode.ALWAYS);
System.out.println(firstResult);
query.setFirstResult(firstResult);
if(fetchSize < fetchOnce)
fetchOnce = fetchSize;
System.out.println(fetchOnce);
query.setMaxResults(fetchOnce);
ArrayList l = (ArrayList)query.list();
ArrayList lClone = (ArrayList)l.clone();
returnValue.addAll(lClone);
l.clear();
l = null;
firstResult += fetchOnce;
fetchSize -= fetchOnce;
session.flush();
session.clear();
Runtime.getRuntime().gc();

}while(fetchSize > 0);

We have done the profiling for the same and found most of the memory is consumed during query.list(); method.

It has positive linear memory consumption graph. The memory is not freed until the whole result set is returned. Should not it free in between i.e after l.clear()

Do i need to free somewhere else so that hibernate does not take much memory.

I am using SQL Server 2005 i think which supports pagination. Why so much memory is consumed even if i am freeing the memory in between.

Regards,
Jamunt

dsiefert · **Joined:** Wed Dec 06, 2006 6:30 pm **Posts:** 8

What is the point in doing all this?

You are copying a list which would initialize the object if it hasn't yet and its related associations. Also, everything is just going into one list at the end. Why don't you either let the query.list() get all the rows at once, or iterate over the rows (using Query.iterate())?

See:

http://www.hibernate.org/hib_docs/v3/reference/en/html/objectstate.html#objectstate-querying-executing-iterate

-David

jamunt · **Joined:** Mon Dec 11, 2006 5:30 am **Posts:** 8

Well actually my using both of the approaches i am getting out of memory error. How can i prevent the same.

Regards,
Jamunt

batmat · **Posted:** Sat Dec 16, 2006 9:33 am

jamunt wrote:

Well actually my using both of the approaches i am getting out of memory error. How can i prevent the same.

Well, if you query 1 million rows, I'm not very surprised you can run out of memory :-). Did you read this : http://blog.hibernate.org/cgi-bin/blosx ... 004/08/26/ ? If you don't flush() AND clear() regularly, the cache will grow until JVM crash... Do you regularly clear the cache ?

jamunt · **Joined:** Mon Dec 11, 2006 5:30 am **Posts:** 8

Thanks for your response. I am calling

session.flush();
session.clear();

in the poted code. But it is still giving out of memory error.
Do i need to do something else too ?

Regards,
Jamunt

batmat · **Posted:** Sun Dec 17, 2006 11:37 am

Yes, I think you should look more carefully at the blog I gave :p. In fact, Gavin describes how to use a scrollable result.

I guess this is why you're obtaining an outofmem : list() tries to load everything in memory, not just the 1000 elements you want to process, so it makes far too much people for your RAM :-).

Quote:

Code:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

ScrollableResults customers = session.getNamedQuery("GetCustomers")
   .scroll(ScrollMode.FORWARD_ONLY);
int count=0;
while ( customers.next() ) {
   Customer customer = (Customer) customers.get(0);
   customer.updateStuff(...);
   if ( ++count % 20 == 0 ) {
      //flush a batch of updates and release memory:
      session.flush();
      session.clear();
   }
}

tx.commit();
session.close();

http://blog.hibernate.org/cgi-bin/blosx ... 004/08/26/

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

consider batmats suggestion , also check out if you can use

http://www.hibernate.org/hib_docs/v3/api/org/hibernate/StatelessSession.html