In hql, probable, but in criteria api needs some more modifications. I am going to take a look at execution plans and do some profiling, and the thing is it doesn't make very huge difference, and since we are in orm world, it is unlikely to cause a problem in anyway. I may be wrong anyway.
On Tue, Dec 16, 2008 at 11:27 PM, Ken Egozi <[email protected]> wrote: > 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 > > > > -- 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 -~----------~----~----~----~------~----~------~--~---
