Hi all,

This might sound crazy but I need some inputs on the hibernate pagination.
I presume that by using the methods

sqlquery.setMaxResults
sqlquery.setFirstResult

we can achive pagination. But the question remains is that these methods will take into effect after the ResultSet is fetched from the database. Corerct me if I am wrong.

This means that the query might be fetching 10,000 records but these filters will be applied upon these 10,000 records and then a small subset will be returned back to the user.

Is there any way that we can fetch only limited number of records and achieve maximum response time?

If you read the source for the database dialects you will see these otpions modify the generated SQL to limit the SQL resultset generated as much as possible. Alternatively, if the JDBC driver is well writen you could use a scroll ing resultset.

David,

Could you eloborate on this.

This is what I do for my pagination.

Session session = getSession();
session.getTransaction().begin();
sqlquery = session.createSQLQuery(s);
sqlquery.setMaxResults(100);
sqlquery.setFirstResult(0)// this will vary as you page by
return sqlquery.list();

What I found is this results in the database query to be fired with all the results got into the ResultSet and then these are applied and I get a sublist out of this.

My question is can I ensure that instead of hibernate getting all 1000 records and returning back only 100, can i execute only 100 and return back that 100 alone.

Thsi was I can ensure that my responses are pretty much faster

does anyone has an idea, I have the same issue,

thanks

We had the same problem for a while; it turned out that our DB "dialect" was not set exactly right. So check that. I assume your DB actually does support "top" or other similar syntax.