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

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

    [ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21220#action_21220 ] 

Fabio Maulo commented on NH-2296:
---------------------------------

The solution is in a TODO inside
QueryLoader.PrepareQueryCommand

> 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: NH2296.Test.patch, 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