[nhibernate-issues] [NHJIRA] Created: (NH-2296) Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql

[Gabe M. (JIRA) <nh...@gm...>]

Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
----------------------------------------------------------------------------------------------

                 Key: NH-2296
                 URL: http://216.121.112.228/browse/NH-2296
             Project:  NHibernate
          Issue Type: Bug
          Components: DataProviders / Dialects
    Affects Versions: 3.0.0.Alpha2
            Reporter: Gabe Moothart
            Priority: Critical
         Attachments: nhib_test.zip

Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.

Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.

If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!

A simple example, from the attached reproduction:

    var qry = sess.CreateQuery("select o from Order o")
                  .SetMaxResults(2)
                  .List<Order>();

    // trigger lazy-loading of products, using subselect fetch.
    string sr = orders[0].Products[0].StatusReason;

Generates the following sql:

    NHibernate: select TOP (@p0) [...]
    from [Order] order0_;@p0 = 2 [Type: Int32 (0)]

     NHibernate: SELECT [...] FROM [Product] products0_ 
     WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)

You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.

This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira