NHibernate Paging performance (Better option)

Question

In that simple case:

public class Person
{
     public int Id {get;set;}
     public int Name {get;set;}
}

• I need generate a Grid with paging and ordering
• My database has about 100k persons

What option has better performance :

1) Get all elemtents in the first time and after take advantage of NHibernate First Level Cache, example:

personRep.FindAll().OrderBy(s =>s.Name).AsPagination(pageNumber,pageSize);

Obs.: AsPagination is a extension method...

2) Get only the actual page from database, example:

   public virtual IList<T> GetPaginedList(int __pageIndex, int __pageSize,out int __total)
    {            
        var _rowCount = Session.CreateCriteria(typeof(T))
            .SetProjection(Projections.RowCount()).FutureValue<Int32>();

        var _results = Session.CreateCriteria(typeof(T))
            .SetFirstResult(__pageIndex * __pageSize)
            .SetMaxResults(__pageSize)
            .Future<T>();

        __total = _rowCount.Value; 
        return _results;
    } 

Answer 1

The 2nd option would be the best fit.

It is useless to retrieve all instances in one go, when you (the user) perhaps doesn't even 'use' all those instances.

If the 'Person' class is a 'heavy' class with lots of associations, it would be even better to create a 'PersonView' class, which contains only the properties you want to show in the Grid.

You don't have to map that PersonView class, you'll simply have to 'import' it, so that NHibernate knows of its existance. Then, you create a query on the Person class, and define that a AliasToBean Transformer has to be used to convert the Person instances to PersonView instances.

By doing so, NHibernate will be able to generate a query which only retrieves the necessary columns from the DB, and will populate PersonView instances with it.