How Optimization in NHibernate query and best performance

Question

I have a project by NHibernate implementation and using Lazy Loading. I have two class in this project : Person and PersonIdentity. Relation between Those two is aggregation, is mean a Person has a one PersonIdentity.

Person mapping is :

<class name="Person" table="Person_Person" >

    <id name="Id" type="Int64" unsaved-value="0">
      <generator class="native" />
    </id>

    <version name="Version" />

    <property name="Name" column="Name"
              type="String(255)"  access="property" not-null="false" />

    <one-to-one name="Identity" property-ref="Person"
      class="Domain.Entities.PersonIdentity,Domain.Entities" cascade="delete" fetch="select" />

</class>

PersonIdentity mapping is :

    <id name="Id" type="Int64" unsaved-value="0" >
      <generator class="native" />
    </id>

    <property name="FirstName" column="FirstName" type="String(255)"  access="property" not-null="false" />

    <property name="LastName" column="LastName" type="String(255)"  access="property" not-null="false" />

    <many-to-one name="Person" column="Person_id_fk" uniqe="true" class="Domain.Entities.Person,Domain.Entities"
      outer-join="auto" fetch="select" access="property" not-null="true" />

  </class>

My problem is in performance. When i execute a query only on Person like this :

var q = SessionInstance.Query<Person>();
IList list = q.ToList<Person>();

I expect only execute

SELECT * FROM Person_Person

But in addition, per person in a database, execute a query like this :

SELECT * FROM Person_Identiyt WHERE Id = 1;
SELECT * FROM Person_Identiyt WHERE Id = 2;
SELECT * FROM Person_Identiyt WHERE Id = 3;
...

And According to lazy approach it is not good, PersonIdentity not should loaded until call it. How can i load only Persons without PersonIdentity for first loading?

There is always an Identity for a Person – Ehsan Nov 09 '11 at 15:12 — Ehsan, Nov 09 '11 at 15:12

score 1 · Answer 1 · answered Nov 09 '11 at 10:49

1

Proxies (lazy-loading) are never used on optional one-to-ones.

A proxy always means that there is something but in case of one-to-one it is possible that no row exists in the other table. And since it's not possible for the proxy to remove itself from the owner property (and set it to null) proxies can't be used.

If your DB makes sure that there is always an Identity (foreign key from Person to Identity) you can add constrained="true" to the one-to-one and NHibernate will use proxies.

Workarounds:

Always load the Identity (with fetch="join") to avoid the select n + 1 problem. (Note: If I remember correctly there might be a bug that still does a n + 1 in that case. See NHibernate Jira.)
Map the reference as a collection and provide a property in your Person class that calls collection.SingleOrDefault(). That way you can use lazy loading.

answered Nov 09 '11 at 10:49

cremor

6,669
1
29
72

I added `constrained="true"` to one-to-one relation for PersonIdentity in Person hbm. But two problem exist : when *insert* and *delete* Person exist a error by this message : not-null property references a null or transient value Domain.Entities.PersonIdentity.Person. But update is true. Why? – Ehsan Nov 09 '11 at 14:40
Why by this change, Domain.Entities.PersonIdentity.Person is transient? But before add `constrained="true"` it was correct? also when `GetByID(x)` for Person still in each case In addition `Select * from Person_Person` executed second query for `Select * from Person_identity where ID = x`. Why? – Ehsan Nov 09 '11 at 15:08
@ehsanzeynali Please post the mapping of `PersonIdentity` and the code you use to save and load the entities. – cremor Nov 10 '11 at 08:28
I added PersonIdentity mapping to question. My Code is : `Person person = new Person(); person.PersonIdentity.FirstName = "Jack"; SessionInstance.Save(person);` – Ehsan Nov 10 '11 at 13:32
1

@ehsanzeynali Err, wait. With a one-to-one to a property (and not a primary key) `constrained="true"` makes no sense. How should NHibernate insert it? `Identity` requires an already inserted `Person` and you said to NHibernate that it should insert `Identity` first (with `constrained="true"`). This solution won't work for you. Some other unrelated points: (1) `type="String(255)"` makes no sense too. I assume you mean `sql-type="String(255)"`. (2) You should add `unique="true"` to the many-to-one side of your one-to-one, otherwise it's not a real one-to-one. – cremor Nov 10 '11 at 13:46
So my problem is not resolved? It is mean that for one-to-one relation can not lazy loading? Also Thank you for two good hints. it is mean that for type of string field should set `nvarchar(255)`? – Ehsan Nov 12 '11 at 04:19
@ehsanzeynali Yes, I fear in your case the only possible workaround to get lazy-loading is the last point in my answer above (mapped as collection). Yes, you can use `nvarchar(255)` (or any other type your DB supports) for `sql-type`. – cremor Nov 14 '11 at 06:32

How Optimization in NHibernate query and best performance

1 Answers1