Besides the dialect issue, in terms of SQL does anybody know What happens if I order by a constant parameter, does the order change indeterministically?
On Wed, Dec 17, 2008 at 12:01 AM, Tuna Toksöz <[email protected]> wrote: > 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! > > -- 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 -~----------~----~----~----~------~----~------~--~---
