Hi,

In hibernate when we use :-

query.setFirstResult (pageParams.getPageNumber() * pageParams.getPageSize()); -> (0*100 = 0)
query.setMaxResults (pageParams.getPageSize()); -> (100)

This will bring the first 100 records.

For the first time, when we click Page 1, hibernate will retrieve the first 0-100 records from the DB. This is fine. When the user clicks the Page 2, hibernate will retrieve 200 records (1-200) among which it will filter and display the next 100 records in this case it is 101-200. Imagine for the page “10” click, will fetch all the 10,000 records from the DB and keep it in its internal memory among which it will filter and display the records from 990-10,000.

This looks like a very expensive operation. Because the query that is genetrated will have only the rowNum<=100 0r 200 0r 10,000. Ideally it should generate something like :-

Select * from ( select * from ….. where rowNum <= 400) where rowNum => 300

Will fetch only 100 records from the DB as same as the first 100 records fetch , rather than fetching the whole 400 records and then doing the filter for 100 records.

Which is not the case here in Hibernate when i verify it with the showsql as "true" in properties. Please , correct me if i have gone wrong.

Thanks.

Guys any response for this? Pls. do respond.