I believe this doesn't exist, but please correct me if I am wrong. If it doesn't exist, please add it to the suggestions list ;)

http://davidhayden.com/blog/dave/archiv ... /2652.aspx

SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications. ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.

If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:

SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10

This is huge. When you SetMaxResults and SetFirstResults with NHibernate it could turn around and transform this into a query of the sort, thus dramatically reducing the data that needs to be fetched. It could then cache portions of the table views that are mostly accessed.

Thx
dB.

This is going to be complicated because the syntax doesn't look like SOME_TEXT<ORIGINAL SQL GOES HERE>MORE_TEXT, but requires one to parse SQL, cut it in pieces, and rebuild a new SQL string out of it.

I am sure you can see the benefits. You could limit this to a more structured query, kind-of like a CreateCriteria. Do you think it's implementable above NHibernate (contrib)?

I believe the problem is more severe as the following example shows. I wanted to reproduce a cool example on Scott Guthrie's Blog "Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0" . This uses the ROW_NUMBER to page thru a large dataset . I added the following to my revised version of his code:

IQuery query = this.Session.GetNamedQuery(queryName);
for (int i = 0; i < args.Length; i++)
query.SetParameter(i, args[i]);
query.SetMaxResults(numForPage);
query.SetFirstResult(startingRow);

Visually the code seemed to work as my objects came back N (page size) at a time but a check of SQL trace revealed that a select top is being generated:

RPC:Completed

exec sp_executesql N'select top 100 listitem0_.ListId as ListId, listitem0_.Name as Name, listitem0_.Category as Category from Lists listitem0_ where (Category=@p0)',N'@p0 nvarchar(4000)',@p0=N'test'

Does not look good unless I am missing something, we are betting the farm on using nHibernate for a large commercial project and performance is a KEY issue so I hope there is an explanation.

You're selecting the 10th page with 10 records per page? That's why it's a TOP 100 - it's all the ten pages. There was no way in SQL to get just the rows between 90 and 100, so NHibernate probably selects the minimum amount of records.

This isn't the worst thing, because few scenarios require you to jump to the 1-millionth record, but this is exactly the reason for my post - today in SQL there's a way to get the useful records, and the useful records only. Hopefully one day in NHibernate, that would be pretty huge.

-dB.

I should have added that this is the last of my "paging" calls and each call looks like query.SetMaxResults(numForPage);
query.SetFirstResult(startingRow); where starting row begins with 0 then next call 10 then 20 .... I believe that ROW_NUMBER is suppossed to allow the records 0-9, then 10-19 .. etc to be returned and not top 10,20 30 .. 100

Right, but this is not implemented in NHibernate. That's why I posted this in the first place - hoping it's on the roadmap.

now we are on the same page ...I was only trying to show that select top just does not cut it and issues like this will drive us in other directions as we will have huge databases. I hope that the issue will be addressed as it woud not seem hard to detect if the SQL is 2005 and use the ROW_NUMBER. Of course there are other .net issues like lack of Generic support .

I think I can live without generics support for a little longer vs. ROW_NUMBER support. You should probably post something to the NHibernate wishlist thread - it's sticky in this forum.

From a very non-initiated look it seem to be close to the way DB2Dialect does it in it's GetLimitString.

I don't have DB2 at hand - does it actually work for DB2?

Alexei

It is the same syntax as with DB2. But it needs an overhaul because I don't think it works perfectly in all scenarios right now.