Hi,

I have 2 millions of rows in a table. Implemented paging using Hibernate Criteria feature. The response time getting worse when the page offset increases. See the below table

Page offset 0 3000 9000 12000 250K 500K 750K 1000k 1250k 1500k
Response time in seconds 34 34 34 34 39 305 310 315 386 384


I think this may not be the problem with Hibernate. Any idea on how to improve the response time for pages who’s offset is greater than half of the total rows?

Thanks
Venkat

Please post your pagination code and what is your jdbc driver?

Hi,



database driver name=Oracle JDBC driver
database driver version=9.2.0.5.0
database product version=Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Number of rows presnet in the DB = 2,000,000

Here is the Code:

public List getPageDataUsingCriteria(String className,
Map<String, Object> matchingCriteria,
String sortCol, int sortOrder,
int pagelength, int pageOffset, String[] colList)
throws IErrorException
{
Criteria criteria = getSession().createCriteria(className);
boolean isSortOrderChangeReq = false;
Set keys = matchingCriteria.keySet();
for(Object key : keys)
{
String propKey = (String) key;
List<Object> propValue = (List<Object>) matchingCriteria.get(propKey);
criteria.add(Expression.in(propKey, propValue));
}

criteria.setProjection(Projections.rowCount());
Object object = criteria.uniqueResult();
int totalCount = ((Integer)object).intValue();
System.out.println("Total Number of Rows = "+totalCount);
if( colList != null && colList.length > 0)
{
ProjectionList proj = Projections.projectionList();
for( String column: colList)
{
proj.add(Projections.property(column));
}
criteria.setProjection(proj);
}

if(sortOrder == IConstants.ASC_ORDER)
{
criteria.addOrder(Order.asc(sortCol));
criteria.addOrder(Order.asc("id")); //Todo
}
else
{
criteria.addOrder(Order.desc(sortCol));
criteria.addOrder(Order.desc("id")); //Todo
}
criteria.setMaxResults(pagelength);
criteria.setFirstResult(pageOffset);

List<Object[]> pageData = criteria.list();
if(pageData == null)
{
return new ArrayList();
}

return pageData;
}

Thanks
Venkat

Maybe try using a cursor instead of setFirstRes() setMaxRes(): http://www.hibernate.org/hib_docs/v3/re ... batch.html

Maybe also a look here: http://www.hibernate.org/314.html ?

Hi,

Thanks for your reply. I tried with scroll and cursor but response is very very slow when number of rows are in million. These two work fins if the number of rows are ~10000. I did some experiment and work for begining and ending pages but not the middle pages. See the below code

if( pageOffset > 100000)
{
isSortOrderChangeReq = isSortOrderChangeReq(totalCount,pageOffset);
System.out.println("Sort Order Change is required = "+isSortOrderChangeReq);
if( isSortOrderChangeReq )
{
System.out.println("Old Sort Order = "+sortOrder);
System.out.println("Old Page Offset = "+pageOffset);
sortOrder = (sortOrder == IConstants.ASC_ORDER)?
IConstants.DESC_ORDER:
IConstants.ASC_ORDER;
pageOffset = totalCount - (pageOffset+pagelength);
System.out.println("New Sort Order = "+sortOrder);
System.out.println("New Page Offset = "+pageOffset);
}
}

with this code last page is taking almost same time as first page but still middle pages are taking too long time to retrieve. Any other suggestion would be great

Thanks
Venkat