Is there a more efficient way of making pagination in Hibernate than executing select and count queries?

Baron Schwartz at MySQLPerformanceBlog.com authored a post about this. I wish there was a magic bullet for this problem, but there isn't. Summary of the options he presented:

1. On the first query, fetch and cache all the results.
2. Don't show all results.
3. Don't show the total count or the intermediate links to other pages. Show only the "next" link.
4. Estimate how many results there are.

If you don't need to display the total number of pages then I'm not sure you need the count query. Lots of sites including google don't show the total on the paged results. Instead they just say "next>".

You can use MultiQuery to execute both queries in a single database call, which is much more efficient. You can also generate the count query, so you don't have to write it each time. Here's the general idea ...

var hql = "from Item where i.Age > :age"
var countHql = "select count(*) " + hql;

IMultiQuery multiQuery = _session.CreateMultiQuery()
    .Add(s.CreateQuery(hql)
            .SetInt32("age", 50).SetFirstResult(10))
    .Add(s.CreateQuery(countHql)
            .SetInt32("age", 50));

var results = multiQuery.List();
var items = (IList<Item>) results[0];
var count = (long)((IList<Item>) results[1])[0];

I imagine it would be easy enough to wrap this up into some easy-to-use method so you can have paginateable, countable queries in a single line of code.

As an alternative, if you're willing to test the work-in-progress Linq for NHibernate in nhcontrib, you might find you can do something like this:

var itemSpec = (from i in Item where i.Age > age);
var count = itemSpec.Count();
var list = itemSpec.Skip(10).Take(10).AsList(); 

Obviously there's no batching going on, so that's not as efficient, but it may still suite your needs?

Hope this helps!

There is a way

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

Reference: FOUND_ROWS()

I know this problem and have faced it before. For starters, the double query mechanism where it does the same SELECT conditions is indeed not optimal. But, it works, and before you go off and do some giant change, just realize it might not be worth it.

But, anyways:

1) If you are dealing with small data on the client side, use a result set implementation that lets you set the cursor to the end of the set, get its row offset, then reset the cursor to before first.

2) Redesign the query so that you get COUNT(*) as an extra column in the normal rows. Yes, it contains the same value for every row, but it only involves 1 extra column that is an integer. This is improper SQL to represent an aggregated value with non aggregated values, but it may work.

3) Redesign the query to use an estimated limit, similar to what was being mentioned. Use rows per page and some upper limit. E.g. just say something like "Showing 1 to 10 of 500 or more". When they browse to "Showing 25o to 260 of X", its a later query so you can just update the X estimate by making the upper bound relative to page * rows/page.

I think the solution depends on database you are using. For example, we are using MS SQL and using next query

select 
  COUNT(Table.Column) OVER() as TotalRowsCount,
  Table.Column,
  Table.Column2
from Table ...

That part of query can be changed with database specified SQL.

Also we set the query max result we are expecting to see, e.g.

query.setMaxResults(pageNumber * itemsPerPage)

And gets the ScrollableResults instance as result of query execution:

ScrollableResults result = null;
try {
    result = query.scroll();
    int totalRowsNumber = result.getInteger(0);
    int from = // calculate the index of row to get for the expected page if any

    /*
     * Reading data form page and using Transformers.ALIAS_TO_ENTITY_MAP
     * to make life easier.
     */ 
}
finally {
    if (result != null) 
        result.close()
}

At this Hibernate wiki page:

https://www.hibernate.org/314.html

I present a complete pagination solution; in particular, the total number of elements is computed by scrolling to the end of the resultset, which is supported by now by several JDBC drivers. This avoids the second "count" query.

here's the way pagination is done in hibernate

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

you can get more info from hibernate docs at : http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#objectstate-querying-executing-pagination 10.4.1.5 and 10.4.1.6 section give you more flexbile options.

BR,
~A