Paging Functionality

tredert · **Joined:** Fri Feb 15, 2008 3:50 am **Posts:** 2

nHibernate version:
1.2.1

Name and version of the database you are using:
MS SQL 2005

Hi all!!! I have a problem using the paging functionality of nHibernate.
I have a Table called "User" which contains about 100 000 records. My intention is now to always load for example 100 records each (paging functionality).
The Code I use for my C# application looks like this, where @index is the first result I want to receive and @count is the number of results.

Code:

return _util.CurrentSession.CreateCriteria(typeof(User)).SetFirstResult(index).SetMaxResults(count).List<User>();

When I execute to code with the parameters @index=99742 and @count=10, the code which i can see in my sql profiler is:

The generated SQL (show_sql=true):

Code:

SELECT top 99752 this_.recid as recid0_0_, this_.firstname as firstname0_0_, this_.lastname as lastname0_0_, this_.email as email0_0_, this_.address as address0_0_, this_.zip as 
zip0_0_, this_.city as city0_0_, this_.telephone as telephone0_0_ FROM users this_

What does this mean: I get 99752 records from my database (at least nhibernate gets all of them, in the code there are really only 10 records). Fetching 99752 records takes a very long time - and that's the problem. It would be much better to optimize the query so that the sql server only returns 10 records. Could anyone please give me some help how i could implement this....

Thanks!!
Best Regard
tredert

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

MS SQL Server 2000 didn't support any mechanism for specifying FIRST and LIMIT like some other RDBMSes so the dialect is designed to request the top start + max_records items and then simply skip over the records up to start before actually hydrating object instances to return. Agreed, this is slow but it's about the best you can expect given the limitations of the query language the database supports.

I know SQL Server 2005 added some query language features to improve this situation, however I don't know whether or not there's an NHibernate dialect that uses them as they're a little arcane. If you're not using the SQL 2005 specific dialect you should try it, but I can't guarantee it will help.

Cheers,

Symon.

sergee · **Joined:** Tue Sep 26, 2006 10:59 am **Posts:** 15

I think you use NHibernate.Dialect.MsSql2000Dialect, but you need NHibernate.Dialect.MsSql2005Dialect. Check and fix param "hibernate.dialect" in your config-file.

tredert · **Joined:** Fri Feb 15, 2008 3:50 am **Posts:** 2

thank you sergee. using the 2005dialect gives the expected results...

best regards