Hi,

Can anyone pls brief me as to how the pagination works internally in Hibernate?
Say for instance I have a Sybase query[select * from employee] that returns 1000 records but i just want to show 10 records per page. Now my question is does Hibernate retrieve all the 1000 results at one shot and keeps i in memory and retrieves only the requested record data through setFirstResult() & setMaxResults() or does it actually queries and returns only 10 record at a time & doesn't keep any thing in memory??? And if so, how does it actually does, am very eager to know this..

Please let me know this logic ..

Thanks in Advance!!

Why don't you simply try? Use setFirstResult and setMaxResults, turn SQL-output on and have a look at the queries fired... If the SQL-dialect used supports pagination you'll see how Hibernate does it.
(For Oracle Hibernate only reads the data needed as you can see in queries.)

Thanks CDillinger.

Can you pls let me know, if there is any document that explains how Hibernate works in the background for pagination in general (or specific to Sybase), because am unable to find any in the net ?

Or probably, a simple clear cut explanation will do.

Hi Arsohoh,

Let me share something about this,
I'm using NHibernate 1.2.1.4000.
One of the method of Hibernate Pagination is to use NHibernate API named ICriteria combine with SetFirstResult() and SetMaxResults() method

Here is the code look like (Line of code added):

1: ICriteria criteria = this.Session.CreateCriteria(typeof(Customer));
2: criteria.SetFirstResult(0).SetMaxResults(20);
3: criteria.Addorder(Order.Desc(utcCreatedTime));
4: criteria.List<Customer >();

The code above will return the result the first 20 records from the table the Customer class mapped to.

And here is further more what is happening in the database level when i looked it using sql server profiler (here i am using sql server database):

exec sp_executesql
N'SELECT TOP 20 IDAlias, CreatedTimeAlias, FROM
(SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ DESC) as row, query.IDAlias, query.CreatedTimeAlias, query.__hibernate_sort_expr_1__ FROM
(SELECT
this_.Id as IDAlias,
this_.utcCreatedTime as CreatedTimeAlias,
this_.utcCreatedTime as __hibernate_sort_expr_1__ FROM
Customer this_
[WHERE... <condition list>]
)
query
)
page
WHERE page.row > 0 ORDER BY __hibernate_sort_expr_1__ DESC',N'@p0 int,@p1 int,@p2 int,@p3 int'<...list of argument>

Analyze:
NHibernate will generate Select statements, One as the most outer query and the others 2 are subquery one another. 3 level subqueries.

Let's start from the most inner query:
First of all, here the inner query will select all records in the tables WITHOUT any limitation (top/limit), if there is condition/WHERE statement applied then the result will be filtered based on this condition.

Next, middle subquery:
This middle subquery will get the result from the first query above, AND THEN add one column that is row number based on sort expression (caused by code line 3). In other words, the sequence is,
1. this query gets the result from first inner most query
2. Sort the result Asc/Desc based on the argument passed from the code.
3. Add RowNumber AFTER the result gets sorted.

Last, the outer query
This query gets its result from the middle query, which the result have been sorted out. And within this query, those SetFirstResult() and SetMaxResults() command are being applied.
Let's start with SetMaxResults(20), this command will generate TOP keyword in the query. Which is very easy to understand. On the above example Select TOP 20 will return only the first 20 records FROM the result generated FROM the middle query above which is the sorted and has row number result on it. In this example, we are showing 20 data in one page.

Now, SetFirstResult(0), this command will generate WHERE page.row > 0 in the most outer WHERE within the most outer query. Remember that "row" is the alias for row number which result from middle query. By playing this method, we can navigate between our result page.
So, here is what it looks like when we combine those two method together:
.SetFirstResult(0).SetMaxResults(20) => give me only twenty record start from record number 0
.SetFirstResult(20).SetMaxresults(20) => give me only twenty record start from record number 20
.SetFirstResult(40).SetMaxresults(20) => give me only twenty record start from record number 40

BUT there is an issue related to this matter,
Back on the inner most query, This is the first query being executed by the database, so the issue occurs when this inner most query returns very large result sets, let's say 500k records. In most cases, especially in web environment, dealing with large data will surely impact the performance, most likely the application would get timeout exception (this is the issue i am dealing with currently).

BUT WHY do i need those 500K result set?
Well, I DON'T, NHibernate DOES. I only need first 20 records. But the way ICriteria.list generates the result is it selects the first 20 records from ALL 500K records.

oops.. sorry, wrong room. This should be in NHibernate Users room. Pls forgive my newbinessity.