nhibernate-core
nhibernate-core copied to clipboard
nhibernate
one-to-one causing N+1 SQL Select statements
I see this problems occurs in #2716 #3292 #850.
But I didnt see any solution for the problem.
I'm using NHibernate v5.5.2 and has an one-to-one relation mapped.
I try to add batch-size in every XML mapping file. It seems to work only with many-to-one relations.
I reproduced this problem in the repository: https://github.com/rodrigo-web-developer/many-to-one-problem
Basically, I have a Product which has a Category (many-to-one). Product has a tax configuration (ProductTax, which is one-to-one).
public class Category
{
public long Id { get; set; }
public string Description { get; set; }
}
public class Product
{
public long Id { get; set; }
public string Description { get; set; }
public double Price { get; set; }
public Category Category { get; set; }
}
public class ProductTax
{
public long Id { get; set; }
public double Tax1 { get; set; }
public double Tax2 { get; set; }
public double Tax3 { get; set; }
public Product Product { get; set; }
}
The mapping files looks like:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="ManyToOneProblem.Entidades" assembly="ManyToOneProblem">
<class batch-size="100" name="Category" lazy="false" >
<id name="Id" column="id" type="long">
<generator class="native" />
</id>
<property name="Description" type="string" length="5000" not-null="false"/>
</class>
<class batch-size="100" name="Product" lazy="false" >
<id name="Id" column="id" type="long">
<generator class="native" />
</id>
<!-- ... other properties .... -->
<many-to-one name="Category" column="CategoryId" fetch="join" lazy="false" class="Category" foreign-key="Fk_Product_Category" index="Idx_Product_Category"/>
</class>
<class batch-size="100" name="ProductTax" lazy="false" >
<id name="Id" column="id" type="long">
<generator class="foreign">
<param name="property">Product</param>
</generator>
</id>
<!-- ... other properties .... -->
<one-to-one class="Product" foreign-key="fk_producttax_product" fetch="join" name="Product"
lazy="false" cascade="all" />
</class>
</hibernate-mapping>
As you can see I added lazy="false" and batch-size="100" to all mappings.
I made a bunch of query examples. Consider that I have 10 products:
Console.WriteLine("====================== QUERY PRODUCTS FIRST ======================");
var queryProducts0 = sessionQuery.Query<Product>().ToList(); // query all
Console.WriteLine("====================== END of query ======================");
Console.WriteLine("====================== Starting query ======================");
var queryProducts = sessionQuery.Query<ProductTax>().ToList(); // query all
Console.WriteLine("====================== END of query ======================");
Console.WriteLine("====================== Starting query 2 - QUERYOVER ======================");
var queryProducts2 = sessionQuery.QueryOver<ProductTax>().List(); // query all
Console.WriteLine("====================== END of query ======================");
Console.WriteLine("====================== Starting query 3 - IQUERYABLE ======================");
var queryProducts3 = from p in sessionQuery.Query<ProductTax>()
join pt in sessionQuery.Query<Product>() on p.Id equals pt.Id
select new ProductTax
{
Id = p.Id,
Product = pt,
Tax1 = p.Tax1,
Tax2 = p.Tax2,
Tax3 = p.Tax3,
};
;
var list = queryProducts3.ToList();
Console.WriteLine("====================== END of query ======================");
The first one (product only) results in 2 selects, so batch-size solved many-to-one problem.
====================== QUERY PRODUCTS FIRST ======================
NHibernate: select product0_.id as id1_1_, product0_.Description as descriptio2_1_, product0_.Price as price3_1_, product0_.CategoryId as categoryid4_1_ from public.Product product0_
NHibernate: SELECT category0_.id as id1_0_0_, category0_.Description as descriptio2_0_0_ FROM public.Category category0_ WHERE category0_.id in (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9);:p0 = 1 [Type: Int64 (0:0:0)], :p1 = 2 [Type: Int64 (0:0:0)], :p2 = 3 [Type: Int64 (0:0:0)], :p3 = 4 [Type: Int64 (0:0:0)], :p4 = 5 [Type: Int64 (0:0:0)], :p5 = 6 [Type: Int64 (0:0:0)], :p6 = 7 [Type: Int64 (0:0:0)], :p7 = 8 [Type: Int64 (0:0:0)], :p8 = 9 [Type: Int64 (0:0:0)], :p9 = 10 [Type: Int64 (0:0:0)]
====================== END of query ======================
The second one (producttax) gives the N+1 selects:
====================== Starting query ======================
NHibernate: select producttax0_.id as id1_2_, producttax0_.Tax1 as tax2_2_, producttax0_.Tax2 as tax3_2_, producttax0_.Tax3 as tax4_2_ from public.ProductTax producttax0_
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 11 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 12 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 13 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 14 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 15 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 16 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 17 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 18 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 19 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 20 [Type: Int64 (0:0:0)]
====================== END of query ======================
The third one, which is one of the suggested in linked issues (using QueryOver makes query create full join select):
====================== Starting query 2 - QUERYOVER ======================
NHibernate: SELECT this_.id as id1_2_2_, this_.Tax1 as tax2_2_2_, this_.Tax2 as tax3_2_2_, this_.Tax3 as tax4_2_2_, product2_.id as id1_1_0_, product2_.Description as descriptio2_1_0_, product2_.Price as price3_1_0_, product2_.CategoryId as categoryid4_1_0_, category3_.id as id1_0_1_, category3_.Description as descriptio2_0_1_ FROM public.ProductTax this_ inner join public.Product product2_ on this_.id=product2_.id left outer join public.Category category3_ on product2_.CategoryId=category3_.id
====================== END of query ======================
The last one, forcing join with LINQ work as well:
====================== Starting query 3 - IQUERYABLE ======================
NHibernate: select producttax0_.id as col_0_0_, product1_.id as col_1_0_, producttax0_.Tax1 as col_2_0_, producttax0_.Tax2 as col_3_0_, producttax0_.Tax3 as col_4_0_, product1_.id as id1_1_, product1_.Description as descriptio2_1_, product1_.Price as price3_1_, product1_.CategoryId as categoryid4_1_ from public.ProductTax producttax0_ inner join public.Product product1_ on (product1_.id=producttax0_.id)
====================== END of query ======================
I have an IRepository<T>, which is a generic interface with public IQueryable<T> Query() method, it calls .Query from ISession. I cant change this code base to use QueryOver because it returns IQueryOver (NHibernate specific interface), not an IQueryable and I dont know the impact of changing ALL ENTITIES to use QueryOver. So I want to use Query() which works fine, but in the case of one-to-one relation it is a problem.
There is a solution for one-to-one relation? If not, there is any work around for this kind of problem?
@rodrigo-web-developer No idea about this one, but a similar issue I am also facing. Can you help me with this issue #3615, and do you have anything about this issue? Please let me know
