Subselect fetched one-to-many relationship does not generate child SQL that takes into consideration Skip() and Take()

Description

When you have a relationship where the children that are sub-selected:

public class ProductMap : ClassMap<Product>
{
public ProductMap()
{
...
HasMany(x => x.Reviews).KeyColumn("ProductID").Fetch.Subselect();
}
}

And you execute a Linq query that pages records using the Skip() and Take() methods:

var products = (from product in session.Linq<Product>().Skip(1).Take(2)
where product.Reviews.Count > 0 && product.Price > 5.99
select product).ToList();

The SQL that gets the parents is properly constrained by the values passed into Take() and Skip():

SELECT TOP 2 ProductID1_0_0_,
Name2_0_0_,
ListPrice3_0_0_
FROM (SELECT this_.[ProductID] as ProductID1_0_0_,
this_.[Name] as Name2_0_0_,
this_.[ListPrice] as ListPrice3_0_0_,
ROW_NUMBER()
OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
FROM Production.Product this_
WHERE (0 /* @p0 */ < (SELECT count(reviews1_.[ProductReviewID]) as y0_
FROM Production.Product this_0_
left outer join Production.ProductReview reviews1_
on this_0_.[ProductID] = reviews1_.[ProductID]
WHERE this_.[ProductID] = this_0_.[ProductID])
and this_.[ListPrice] > '5.99' /* @p1 */)) as query
WHERE query.__hibernate_sort_row > 1
ORDER BY query.__hibernate_sort_row

But the SQL that gets the children doesn't take into account the Skip() and Take() methods (Although it does take into consideration the where clause):

SELECT reviews0_.[ProductID] as ProductID4_1_,
reviews0_.[ProductReviewID] as ProductR1_1_,
reviews0_.[ProductReviewID] as ProductR1_1_0_,
reviews0_.[ReviewerName] as Reviewer2_1_0_,
reviews0_.[Comments] as Comments3_1_0_
FROM Production.ProductReview reviews0_
WHERE reviews0_.[ProductID] in (select this_.[ProductID]
FROM Production.Product this_
WHERE (0 /* @p0 */ < (SELECT count(reviews1_.[ProductReviewID]) as y0_
FROM Production.Product this_0_
left outer join Production.ProductReview reviews1_
on this_0_.[ProductID] = reviews1_.[ProductID]
WHERE this_.[ProductID] = this_0_.[ProductID])
and this_.[ListPrice] > '5.99' /* @p1 */))

This results in VERY slow performance on tables with a large number of records that are not filtered by the paged constraints.

I'm just using the AdventureWorks db in the attached code to demo the problem, but the child table in our case contains 1,366,916 records so it's quite a hit.

Environment

None

Attachments

1

Activity

Mike 
February 1, 2010 at 6:03 PM

Hmmm, looks like it might be the same issue. Not 100% sure though. BTW, I'm using SQL Server 2005.

Details

Assignee

Reporter

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created February 1, 2010 at 5:49 PM
Updated February 1, 2010 at 6:03 PM
Who's Looking?