Hi,

In my Scenario, i have done pagination using spring/Hibernate which displays 100 results per page and also specify ascending order on lastname field
startIndex = 0;maxDisplay=100;

Order order = Order.asc("lastname");
criteria.addOrder(order);
criteria.setFirstResult(Integer.parseInt(startIndex));
criteria.setMaxResults(Integer.parseInt(maxDisplay));

For the first time it gives me quick response but when i click next it is giving me output in almost 1 minute

Here are the 2 queries which HQL generates:

1st time(Works fine)-: select * from ( select this_.id as id5_2_, this_.title as title5_2_, this_.last_name as last3_5_2_ from web_visitors this_, web_visitor_site webvisitor1_, sites site2_ where this_.id=webvisitor1_.web_visitor and webvisitor1_.site=site2_.id and site2_.id=? order by this_.last_name asc ) where rownum <= ?

2nd time(Takes time)-: select * from ( select row_.*, rownum rownum_ from ( select this_.id as id5_2_, this_.title as title5_2_, this_.last_name as last3_5_2_ from web_visitors this_, web_visitor_site webvisitor1_, sites site2_ where this_.id=webvisitor1_.web_visitor and webvisitor1_.site=site2_.id and site2_.id=? order by this_.last_name asc ) row_ ) where rownum_ <= ? and rownum_ > ?

Note: If i remove order by from this query and runs in Database it gives me quick output but same query take 1 minute time with order by clause

i have 50000-100000 records in oracle DB

Can any one Suggest what can be done in this scenario

Thanks in Adv
Samy