who said that TOP is faster than BETWEEN? unless a PM from SQL-Server (or Itsik Ben-Gan) says so I'll find it very hard to believe. The book-keeping is needed sure.
and about parameters book-keeping - why not use named parameters On Tue, Dec 16, 2008 at 11:22 PM, Tuna Toksöz <[email protected]> wrote: > 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! > > > > > -- Ken Egozi. http://www.kenegozi.com/blog http://www.delver.com http://www.musicglue.com http://www.castleproject.org http://www.gotfriends.co.il --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
