Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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

Description

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.

Environment

None

Attachments

3

Activity

Show:

Dawid Ciecierski 
May 17, 2014 at 9:35 AM

Why has this issue been closed? As reported in I'm still seeing similar behaviour in 3.3.3SP1. With some digging around it turns out that even though this was supposedly fixed, the patch was later reversed with this github commit .

Would love to see this getting some attention, because as the OP mentioned this can load a great deal of data from the database into the session — effectively the entire child collection table — even when usually we only want a tiny subset of that data.

Fabio Maulo 
July 30, 2011 at 2:11 PM

Closed after final release of NH3.2.0GA

AgrineiS 
June 25, 2011 at 3:43 AM

It still fails if the main query has an Order clause. I'm attaching a test that I've added to the ones in SubselectFetchFixture.cs

Fabio Maulo 
May 30, 2011 at 7:06 AM

The solution is in a TODO inside
QueryLoader.PrepareQueryCommand

AgrineiS 
October 8, 2010 at 5:36 PM

Isn't this related to NH-1123, and ?

Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created August 19, 2010 at 10:14 AM
Updated May 17, 2014 at 9:35 AM
Resolved June 18, 2011 at 5:59 AM
Who's Looking?