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.
