> 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.
The only thing I can think of what might be in the second where is
an aggregate based predicate, but unless you post the real query with real
code, it's guesswork.
The thing is that a where valid on the inner query is also valid on
the outer query, except when the predicate is aggregating data.
Also, it's a bit odd really. The problem is that paging is used to
obtain a subset from a bigger resultset. So the bigger resultset is the
_final_ set. It's just too big so you want to obtain a sub-page from it. But
if you place a where on that subset, what do you do when some or worse case:
all rows are not matching the predicate? Then you get less rows back for a
page you requested (or none at all).
FB
> 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%2bunsubscr...@googlegro
> > > nhusers+ups.com >
> > > .
> > > 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].
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
--
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.