Pagination problem with MSSQL

jskathirvel · **Joined:** Wed Feb 04, 2009 11:42 am **Posts:** 3

Hi,

We are using MSSQL Database and a particular table is having more than 50000 records. When we navigate to last page using pagination, we are getting heap memory out of space. we are showing 20 records per page, but the query fired is having top 50000 from table. It seems hibernate fetching all the 50000 records and after that showing only 20 records. Due to this we are getting heap memory out of space.

Is there any option to fetch only 20 records as like in select * from table limit 49980, 50000 of MySQL.

Thanks in advance.

Kathirvel Subramaniam

mmerder · **Posted:** Wed Feb 04, 2009 12:55 pm

Did you use query.setMaxResults(X) and query.setFirstResult(Y)? If yes, hibernate only selects these rows. you could also call session.clear() after each read, so that the session-cache gets cleared.

jskathirvel · **Joined:** Wed Feb 04, 2009 11:42 am **Posts:** 3

Thanks for reply.

we achieved pagination using query.setFistResult(49980) and query.setMaxResult(50000) to get the 20 records, but it seems while fetching the record itself we are getting heap memory out of space. That is before executing session.clear().

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

setMaxResult takes as argument the amount of data you want to fetch, so instead of 50000 it should be 20. Also make sure to do flushing and clearing the session after some interval to free the space.
For Example:

Code:

int pageSize = 20; // how many objects to retrieve in one query 

int numRows = 50 000; // get this via a count() query 

for (int i=0; i < numRows/pageSize; i++) { 
    // query such that everything you need is retrieved in one query 
    Query q = sess.createQuery("QueryString"); 
    q.setFirstResult(i*pageSize); 
    q.setMaxResults(pageSize); 
    List data = q.list(); 
    // Iterate over data and do something, then evict objects and can also call session.clear and session.flush
} 

Rating appreciated if it helped.

jskathirvel · **Joined:** Wed Feb 04, 2009 11:42 am **Posts:** 3

I am sorry, i have entered wrongly. We are setting maxResult as the number of records per page.

That is, query.setFistResult(49980) and query.setMaxResult(20) to get the 20 records.

When i refer the SQLServerDialect to see how the adding limiting string, they are adding top key word with sum of first result and max result.

That means they are actually fetching the 50000 records and then sub listing the 20 which leads to out of memory error.

Have anybody come accross the problem?

Leonm · **Joined:** Wed Mar 04, 2009 8:58 pm **Posts:** 1

I seem to have exactly the same problem.

I fired up a debugger and put some breakpoints in the HSQLDialect class. Even though the supportsLimit() method executes and returns true, the getLimitString() method is never reached.

I tried to step through the code a but further but couldn't find anything useful.

Using hibernate core 3.3.0.sp1 (also happens wth version 3.2.1) with JPA.