select {fields}
from
(
SELECT {fields}, ROW_NUMBER() over({whatever}) as ROWNUM
FROM (blah blah blah)
)
where ROWNUM BETWEEN first AND last
you need to selelect using TOP in order to have faster queries, but this is
not the problem. The thing is you move the order by into OVER() and this
changes the order of the parameters, this is why we have to bookkeep it.
On Tue, Dec 16, 2008 at 11:18 PM, Tuna Toksöz <[email protected]> wrote:
> Well, it needs some modifications, not like the one I tried obviously.
> Parameter bookkeeping creates complications, and been problematic over the
> time.I will study it too, but this one is just for learning.
>
>
> On Tue, Dec 16, 2008 at 11:06 PM, Ken Egozi <[email protected]> wrote:
>
>> I don't get the 'problem' with MSSQL2005 paging.
>>
>> why not simply go for
>>
>> select {fields}
>> from
>> (
>> SELECT {fields}, ROW_NUMBER() over({whatever}) as ROWNUM
>> FROM (blah blah blah)
>> )
>> where ROWNUM BETWEEN first AND last
>>
>>
>>
>> On Tue, Dec 16, 2008 at 11:00 PM, Tuna Toksöz <[email protected]> wrote:
>>
>>> And of course, this won't go into NH codebase, since it is really hacky!
>>> :)
>>>
>>>
>>> On Tue, Dec 16, 2008 at 10:45 PM, Tuna Toksöz <[email protected]> wrote:
>>>
>>>> Not directly related to NHibernate, but it am playing with mssql 2005
>>>> dialect for nhibernate.
>>>>
>>>> As you know, in order to use paging, we have to move ORDER BY clauses
>>>> into OVER (HERE) in order to get order numbers.
>>>>
>>>>
>>>> My thinking is that instead of moving ORDER BY clause, I plan to use a
>>>> structure similar to this
>>>>
>>>>
>>>> select top 10 id_0_1, name_0_1 from (select row_number() over (order
>>>> current_timestamp) as row,this_.id as id_0_1, this_.name_0_1 from table
>>>> this_ order by this_.name)
>>>>
>>>> obviously this won't work as we can't nest a select clause if it has
>>>> order by embedded, it says TOP clause is needed.
>>>>
>>>> i then tried
>>>>
>>>>
>>>> "select top 10 id_0_1, name_0_1 from (select TOP " + long.MaxValue + "
>>>> row_number() over (current_timestamp) as row,this_.id as id_0_1,
>>>> this_.name_0_1 from table this_ order by this_.name) where row>30"
>>>>
>>>> The questions are:
>>>>
>>>> What happens if I add TOP VERYBIGINTEGER(long.MaxValue) in terms of
>>>> performance, I don't think it really makes an effect, besides looking
>>>> really
>>>> ugly.
>>>> What happens if order by current_timestamp? I heard that it's precision
>>>> may not be enough accurate, but assuming this value is calculated row by
>>>> row, some rows will share the same number, and some higher when the row
>>>> number increases.
>>>> What happens if I order by a constant parameter, in terms of performance
>>>> again?
>>>>
>>>> Thank you all for your attention.
>>>>
>>>>
>>>> --
>>>> Tuna Toksöz
>>>> http://tunatoksoz.com
>>>>
>>>> Typos included to enhance the readers attention!
>>>>
>>>>
>>>
>>>
>>> --
>>> Tuna Toksöz
>>> http://tunatoksoz.com
>>>
>>> Typos included to enhance the readers attention!
>>>
>>>
>>>
>>>
>>
>>
>> --
>> Ken Egozi.
>> http://www.kenegozi.com/blog
>> http://www.delver.com
>> http://www.musicglue.com
>> http://www.castleproject.org
>> http://www.gotfriends.co.il
>>
>> >>
>>
>
>
> --
> Tuna Toksöz
> http://tunatoksoz.com
>
> Typos included to enhance the readers attention!
>
>
--
Tuna Toksöz
http://tunatoksoz.com
Typos included to enhance the readers attention!
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
