I've been trying to map a one-to-one association and I am still running into the problem of n+1 selects even after switching to nhibernate 2.1.0.
I'm sorry this post is so long but it seems to be the only way to get this issue across.
What i'm trying to implement is a one to one between a person and a customer. Person in the system may or may not be customers. But each customer must be related to a customer
Code:
class Person
{
string firstname;
....
Customer customer;
}
class Customer
{
string Number
Customer(Person person) { this.person = person; }
...
Person person;
}
I defined the mappings as follows
Code:
<class name="Person" table="Person" >
<id name="Id">
<property name="FirstName"/>
...
<one-to-one name="Customer" class="Customer" property-ref="Person"/>
</class>
<class name="Customer" table="Customer">
<id name="Id"/>
<property name="Number"/>
...
<many-to-one class="Person" name="Person" column="PersonID" not-null="true"
unique="true"/>
</class>
Everything seems ok when I query from the <one-to-one> end
Code:
Person person = session.Get<Person>(1);
Console.WriteLine("Firstname={0}, Lastname={1}", person.FirstName, person.LastName);
if (tope.Customer != null)
{
Console.WriteLine("After Get");
Console.WriteLine("Customer Number={0}", person.Customer.CustomerNumber);
}
The output is as follows
Code:
SELECT person0_.id as id3_1_, person0_.Version as Version3_1_, person0_.FirstName as FirstName3_1_, person0_.LastName as LastName3_1_, customer1_.id as id0_0_, customer1_.Version as Version0_0_, customer1_.Number as Number3_0_0_, customer1_.PersonID as PersonID0_0_ FROM Tests.dbo.Person person0_ left outer join Tests.dbo.customer customer1_ on person0_.id=customer1_.PersonID WHERE person0_.id=@p0;@p0 = 1
Firstname=John, Lastname=Doe
After Get
Number=1111
The customer is always eagerly loaded no matter what the settings are on the <one-to-one>.
The only thing that can be changed is to stop it being fetched by an outer join by including outer-join="false" or fetch="select" on the <one-to-one> mapping.
I don't mind the outer join really since it dosen't result in an extra select query for the customer, the customer object is completely hydrated. Even navigating person.Customer.Person... does not result in an extra query. Please note that constrained="true" does not apply here because not every person object is a customer as well, but even setting constrained="true" does not seem to change anything.
Meanwhile I thought lazy loading <one-to-one> associations was one of the issues resolved by version 2.1 of nhibernate and version 3 of hibernate. This was actually my reason for migrating from version 2.0 to 2.1. Please correct me if i'm wrong on this. What can be done to make the <one-to-one> load the related object lazily, i.e. with a separate query only when the association is accessed.
The main problem is however the <many-to-one> end of the association. It seems this always results in a redundant query no matter what I do. I have tried various combinations of settings in the mapping files. I have also tried specifying all kinds of "..join fetch.." in hql and also using SetfetchMode in the criteria queries bu nothing seems to work. There is always a redundant query. This can get really bad when trying to fetch all customers. A separate query is run for each row in the customers record. This is a very basic requirement i.e. fetching all customers and getting the person as well which from a logical viewpoint suggests a simple join but it ends up being horribly ineffective. Right now I just so confused about all the options I've tried and I'm really frustrated with something that seems so simple. I'm not even sure I had this problem in version 2.0.
Examples of querying the <many-to-one> end
Code:
{
Customer customer = session.Get<Customer>(4);
Console.WriteLine("Before assert");
Assert.IsNotNullcustomer.Person);
Console.WriteLine("After assert, about to access person property");
Assert.IsNotNull(customer.Person.FirstName);
}
Code:
SELECT customer0_.id as id0_0_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_ FROM Tests.dbo.customer customer0_ WHERE customer0_.id=@p0;@p0 = 4
Before assert
After assert, about to access person property
SELECT person0_.id as id3_1_, person0_.Version as Version3_1_, person0_.FirstName as FirstName3_1_, person0_.LastName as LastName3_1_, person0_.Gender as Gender3_1_, customer1_.id as id0_0_, customer1_.Version as Version0_0_, customer1_.Number as Number3_0_0_, customer1_.PersonID as PersonID0_0_ FROM Tests.dbo.Person person0_ left outer join Tests.dbo.customer customer1_ on person0_.id=customer1_.PersonID WHERE person0_.id=@p0;@p0 = 1
SELECT customer0_.id as id0_0_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_ FROM Tests.dbo.customer customer0_ WHERE customer0_.PersonID=@p0;@p0 = 1
Three queries are actually run here. The first one for the Customer details the next for the person in this fetched customer, and the third for the customer in the person just fetched. I am surprised that this is happening since a separate query was not issued in the previous scenario which fetched the customer for a person using a join. In fact an outer join is actually used here as well but it appears that the customer property of the person is not set this time. Adding constrained="true" also does not change this query.
The only improvement I seem to be able to make is to reduce the queries to two by adding fetch="join" to the <many-to-one> or doing a "join fetch" in a hql query or setting a FetchMode in a criteria query
Changing the many-to-one mapping to:
Code:
<many-to-one class="Person" name="Person" column="PersonID" not-null="true" unique="true" fetch="join"/>
results in:
Code:
SELECT customer0_.id as id0_1_, customer0_.Version as Version0_1_, customer0_.Number as Number3_0_1_, customer0_.PersonID as PersonID0_1_, person1_.id as id3_0_, person1_.Version as Version3_0_, person1_.FirstName as FirstName3_0_, person1_.LastName as LastName3_0_ FROM Tests.dbo.customer customer0_ inner join Tests.dbo.Person person1_ on customer0_.PersonID=person1_.id WHERE customer0_.id=@p0;@p0 = 4
SELECT customer0_.id as id0_1_, customer0_.Version as Version0_1_, customer0_.Number as Number3_0_1_, customer0_.PersonID as PersonID0_1_, person1_.id as id3_0_, person1_.Version as Version3_0_, person1_.FirstName as FirstName3_0_, person1_.LastName as LastName3_0_ FROM Tests.dbo.customer customer0_ inner join Tests.dbo.Person person1_ on customer0_.PersonID=person1_.id WHERE customer0_.PersonID=@p0;@p0 = 1
Before assert
After assert, about to access person property
After accessing person property
This time just two queries both of which fetch the customer and join the person also fetching the person's properties at the same time.
The two queries are both run eagerly so all the writes to console come after the queries.
The two queries are almost exactly alike. But why is the second redundant query required at all.
when I use the "join fetch" in hql query similar results as well
Code:
session.CreateQuery("select c from Customer c join fetch c.Person where c.id = 4").UniqueResult<Customer>();
Code:
select customer0_.id as id0_0_, person1_.id as id3_1_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_, person1_.Version as Version3_1_, person1_.FirstName as FirstName3_1_, person1_.LastName as LastName3_1_ from Tests.dbo.customer customer0_ inner join Tests.dbo.Person person1_ on customer0_.PersonID=person1_.id where customer0_.id=4
SELECT customer0_.id as id0_0_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_ FROM Tests.dbo.customer customer0_ WHERE customer0_.PersonID=@p0;@p0 = 1
Before assert
After assert, about to access person property
After accessing person property
Is there any solution to this problem?
What am I doing wrong?
This problem is far worse when I try to fetch all customers.
One select is done to retrieve all customer information and joining the person information.
Then multiple selects are issued, one for each customer to retrieve customer information for each person in the first query results
Code:
public void FetchAllCustomers()
{
IList<Customer> extras = session.CreateQuery
("select c from Customer c join fetch c.Person").List<Customer>();
Console.WriteLine("After fetching customers");
foreach (Customer customer in customers)
Console.WriteLine("{0}, {1}", customer.Number, customer.Person.FirstName);
}
Code:
select customer0_.id as id0_0_, person1_.id as id3_1_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_, person1_.Version as Version3_1_, person1_.FirstName as FirstName3_1_, person1_.LastName as LastName3_1_ from Tests.dbo.customer customer0_ inner join Tests.dbo.Person person1_ on customer0_.PersonID=person1_.id
SELECT customer0_.id as id0_0_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_ FROM Tests.dbo.customer customer0_ WHERE customer0_.PersonID=@p0;@p0 = 1
SELECT customer0_.id as id0_0_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_ FROM Tests.dbo.customer customer0_ WHERE customer0_.PersonID=@p0;@p0 = 2
SELECT customer0_.id as id0_0_, customer0_.Version as Version0_0_, customer0_.Number as Number3_0_0_, customer0_.PersonID as PersonID0_0_ FROM Tests.dbo.customer customer0_ WHERE customer0_.PersonID=@p0;@p0 = 3
After fetching customers
1111, John
2222, Mike
3333, Peter
Please somebody save me.
Login
FAQ
Search
