On Thu, Sep 2, 2010 at 13:59, Joseph Daigle <[email protected]> wrote:

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

BS.


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

This query:

session.CreateCriteria<Foo>().Add(Restrictions.Eq("Data",
"abc")).AddOrder(Order.Asc("Bar")).SetFirstResult(20).SetMaxResults(10).List();


Generates this SQL (using the 2008 dialect):
  SELECT
      TOP (@p0) Id0_0_,
      Data2_0_0_,
      Bar0_0_
  FROM
      (SELECT
          this_.Id as Id0_0_,
          this_.[Data] as Data2_0_0_,
          this_.Bar as Bar0_0_,
          ROW_NUMBER() OVER(
      ORDER BY
          this_.Bar) as __hibernate_sort_row
      FROM
          Foo this_
      WHERE
          this_.[Data] = @p2) as query
  WHERE
      query.__hibernate_sort_row > @p1
  ORDER BY
      query.__hibernate_sort_row;
  @p0 = 10 [Type: Int32 (0)],
  @p1 = 20 [Type: Int32 (0)],
  @p2 = 'abc' [Type: String (100)]

- WHERE is in the inner select
- Page and count are parameterized
- Filter parameter has the correct size

It's hard to optimize more than that.

  Diego

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