BTW, Fabio suggested that we change positional parameters into named ones, but probably nobody could find time for it yet.
On Tue, Dec 16, 2008 at 11:38 PM, Tuna Toksöz <[email protected]> wrote: > 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! > > -- 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 -~----------~----~----~----~------~----~------~--~---
