Hibernate version: Hibernate 3.2.4
Name and version of the database you are using:mySql 5.0
Hi everyone, I'm locking for a fast paging method. I have a table with 3'000.000 of rows, it has one pk and without fk. I've used criteria.setFirstResult and setMaxResults methods like it shows:
Code:
public List selectTABLE_ORIGIN(String pUser, int PageSize, int PageId)throws ServiceException
{
StatelessSession sess_w = getStateLessSession(pUser);
int start_w = (PageId-1)*PageSize;
int rowNum_w = PageSize;
try
{
Criteria criteria_w = sess_w.createCriteria (TABLE_ORIGIN_Impl.class);
List list_w = criteria_w.setFirstResult(start_w).setMaxResults (rowNum_w).list();
return list_w;
}catch(Exception e){
throw new ServiceException(e.getMessage(), e);
}finally{
closeSession();
}
}
I'm calling this method to get the rows, the PageZise is 500 and the PageId goes growing each time i call the method. The 10 fist times are fast, but it goes down after that and the performance is bad. i've taken the time and i could see that it was taking 13 minutes to get 100.000 rows, it means that 3'000.000 would take more or less 6 hours and obviously it can not be possible.
So i tried to implement ScrollableResults in order to take advantage of server-side cursors like it shows:
Code:
public void selectTABLE_ORIGINAndPut(String pUser)throws ServiceException
{
StatelessSession sess_w = getStateLessSession(pUser);
try
{
String hql = "from TABLE_ORIGIN_Impl";
Query query = sess_w.createQuery(hql);
ScrollableResults registries_w = query.scroll(ScrollMode.FORWARD_ONLY);
while ( registries_w.next() )
{
TABLE_ORIGIN table_w = (TABLE_ORIGIN) registries_w.get(0);
}
}catch(Exception e){
e.printStackTrace();
throw new ServiceException(e.getMessage(), e);
}finally{
closeSession();
}
}
but it throws a java.lang.OutOfMemory error and I couldn't get any registry.
can someone tell me if there is a better option to do that or if there is any mistake in my code?
Thanks
Login
FAQ
Search