Getting the TOP rows WITHOUT paging code

cbryden · **Joined:** Mon Mar 17, 2008 9:31 am **Posts:** 6

Hi

I am fairly new to NHibernate so this is definitely a noob question. I am running NHibernate 1.2.1 with SQL Server 2005.

I am trying to get the TOP 100 rows from my purchase table. Below is a snippet of the code:

Code:

            IQuery query = session.CreateQuery("from Purchase p order by p.Date DESC");
            query.SetMaxResults(100);
            IList<Purchase> purchases = query.List<Purchase>();

The SQL I would expect would be something like:

Code:

SELECT TOP 100 p.PurchaseID,
            p.PlayerID,
            p.CasinoID,
            p.PurchaseStatus,
            p.PurchaseBrandID,
            p.Date,
            p.Amount
FROM tb_Purchase p
ORDER BY p.Date DESC

Instead, the SQL that is being generated is

Code:

SELECT TOP 100 PurchaseID6_, Purchase2_6_, Date6_, Amount6_, CasinoID6_, PlayerID6_, Purchase7_6_ 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ DESC) as row, query.PurchaseID6_, query.Purchase2_6_, query.Date6_, query.Amount6_, query.CasinoID6_, query.PlayerID6_, query.Purchase7_6_, query.__hibernate_sort_expr_1__ 
      FROM (select purchase0_.PurchaseID as PurchaseID6_, purchase0_.PurchaseStatus as Purchase2_6_, purchase0_.Date as Date6_, purchase0_.Amount as Amount6_, purchase0_.CasinoID as CasinoID6_, purchase0_.PlayerID as PlayerID6_, purchase0_.PurchaseBrandID as Purchase7_6_, purchase0_.Date as __hibernate_sort_expr_1__ 
            from tb_Purchase purchase0_) query 
      ) page 
WHERE page.row > 0 ORDER BY __hibernate_sort_expr_1__ DESC

Is there a way to tell Nhibernate that I do not want to page and therefore it should simplify the query? Possibly I am not using the correct methods here?

Any help will be greatly appreciated

Thanks
Craig

FlukeFan · **Posted:** Tue Mar 25, 2008 10:17 am

Hi Craig,

I believe if you use the SQL2000 dialect instead of the SQL2005 dialect you will get the results you want. However, it will mean that if you want rows 90->100 of a result set, then NHibernate will pull back 100 rows and skip the first 90.

The SQL generated by NHibernate is not pretty ... but I think it is correct. Why would you want to change it?

The only other way I know of changing this would be to implement your own dialect (by subclassing the 2005 one, say).

Regards,
Richard

cbryden · **Joined:** Mon Mar 17, 2008 9:31 am **Posts:** 6

Hi Richard

Thanks for the reply

If I profile the two statements listed above, the first one (the one I would expect) is significantly faster than the second. It does stand to reason that it would be faster because it is not using as many SQL functions and sub queries. In this case the difference is almost 250%!

FlukeFan · **Posted:** Tue Mar 25, 2008 10:35 am

Hi Craig,

I tried it on a system I'm working on, and it made no difference.

That is, while the query plans were different, and the one with paging was more complex, they both put 100% of the work into a Clustered Index Scan.

You could maybe pass it over to your DBA for further investigation. In the meantime, reverting to the 2000 Dialect might help.

Regards,
Richard

Woil · **Posted:** Tue Mar 25, 2008 4:39 pm

If you have only 100 rows in the table, the simpler query might indeed be faster. When you're trying to select records 10,100 to 10,200 out of a table with 100,000 rows the second one will be much faster.

-Will

cbryden · **Joined:** Mon Mar 17, 2008 9:31 am **Posts:** 6

Hi Richar/Will

Thanks for your replies. I suspect my dev system was under load from other users at the time. I got some dedicated time on the system this morning and the queries now run in almost the exact same amount of time :)