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.