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