ah Joseph...
please don't forget to ask to your DBA if he sent the issue to the MsSQL
team about the pagination syntax used by MsSQL.

On Thu, Sep 2, 2010 at 2:22 PM, Fabio Maulo <[email protected]> wrote:

> When you find something so complicated to query perhaps the wrong thing is
> not in the SQL.
> btw, you can use your SP with NH and have your nice OO result.
>
>
> On Thu, Sep 2, 2010 at 2:16 PM, Joseph Daigle <[email protected]>wrote:
>
>> And the crazy thing about this particular pagination example:
>>
>> We have a WHERE clause on the "inner select" which actually generates
>> the page, and then we need a WHERE clause on that resulting page (this
>> is a complicated use case involving getting back altered rows on a
>> visible page). So the common table expression in the latter example is
>> the cleaner, and possibly better performing, query. But we couldn't
>> ever get NHibernate to generate that.
>>
>> I'd replace it with a stored procedure, except that the WHERE clause
>> is not always the same. There are maybe 50-100 distinct query plans
>> that are generated based on combination of application specified
>> filtering and sorting. The former query, as generated by NHibernate,
>> performs well enough as long as indexes are tuned.
>>
>> --
>> Joseph Daigle
>>
>> On Sep 2, 1:06 pm, Fabio Maulo <[email protected]> wrote:
>> > aaaaaaaahhhh pagination.... nice example!!
>> > Ask to your DBA if he sent an issue to MsSQL team asking for a
>> > human-oriented-syntax for pagination stuff.
>> > I mean something similar to ORACLE, Firebird, MySQL, Postgre etc. etc.
>> or
>> > even something like SqlCe4...
>> > We can accept even something like this:
>> > SELECT SKIP 100 TAKE 25 ........... FROM ...... WHERE ..... ORDER BY
>> ......
>> >
>> > On Thu, Sep 2, 2010 at 1:59 PM, Joseph Daigle <[email protected]
>> >wrote:
>> >
>> >
>> >
>> >
>> >
>> > > Hi Frans,
>> >
>> > > >         care to share the name of this community member? :)
>> >
>> > > Send me an e-mail and I'll share it offline (I don't want to burn
>> > > anyone unnecessarily).
>> >
>> > > >         care to share one as an example? I.o.w. I think it's more of
>> an
>> > > edge
>> > > > case when you run into these limitations, with any o/r mapper btw.
>> Often
>> > > > these queries are using temp tables, cursors etc.
>> >
>> > > They are edge cases. And as I said, we can drop down to stored
>> > > procedures when necessary to resolve these edge cases.
>> >
>> > > >         what does 'not stable' mean, exactly? Correlated subqueries
>> are
>> > > > either correct, or not, but 'unstable' is IMHO a term not used in
>> > > > combination of correlated subqueries.
>> >
>> > > "not stable" is a term the consultant used, which I believe means that
>> > > the sql statement performance will not scale linearly as the load
>> > > increases and/or table size increases
>> >
>> > > >         An example would be great.
>> >
>> > > The example used:
>> >
>> > > SELECT ...
>> > > FROM ...
>> > > WHERE [Id] IN (
>> > >  SELECT TOP 200
>> > >  [Id]
>> > >  FROM ...
>> > >  WHERE ...
>> > >  ORDER BY ...
>> > >  )
>> > > WHERE ...
>> >
>> > > versus
>> >
>> > > SELECT ...
>> > > FROM (
>> > >  SELECT ...,
>> > >    ROW_COUNT() OVER(ORDER BY...) AS [row_num]
>> > >  FROM ...
>> > >  WHERE ...
>> > > )
>> > > WHERE ...
>> > > AND [row_num] < 200
>> >
>> > > The former is what we can generate with NHibernate using Criteria. The
>> > > second we cannot.
>> >
>> > > Thanks,
>> >
>> > > --
>> > > Joseph Daigle
>> >
>> > > --
>> > > 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]<nhusers%[email protected]>
>> <nhusers%[email protected]<nhusers%[email protected]>>
>> > > .
>> > > For more options, visit this group at
>> > >http://groups.google.com/group/nhusers?hl=en.
>> >
>> > --
>> > Fabio Maulo
>>
>> --
>> 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]<nhusers%[email protected]>
>> .
>> For more options, visit this group at
>> http://groups.google.com/group/nhusers?hl=en.
>>
>>
>
>
> --
> Fabio Maulo
>
>


-- 
Fabio Maulo

-- 
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.

Reply via email to