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.
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.