Hello,
I'm fairly new to NHibernate and am using it in a thick client Windows
Forms application. Until recently NHibernate solved 90% of my data
persistence requirements; the remaining 10% being relatively easy to
solve because NHibernate can be easily extended using configuration
and custom queries. However, there are a couple of scenarios where
NHibernate does not compete with similar hand rolled solutions. For
these scenarios, I would like to figure out:
a) Am I using NHibernate inappropriately?
b) Is NHibernate the right tool for this problem?
c) Is my model and/or mappings flawed?
Before I go on to explain my problems I will explain my model as
simply as possible.
I have 3 entities:
Hierarchy
Member
Attribute
Each Hierarchy has a collection of Members.
Each Member has a collection of Members and a collection of
Attributes.
As you can see from the mapping files below, the Member contains a
parent-child association with itself. This means my model can be
arbitrarily deep with N number of levels.
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateTest" namespace="NHibernateTest" default-
lazy="false">
<class name="NHibernateTest.Hierarchy, NHibernateTest"
table="HIERARCHIES" lazy="false">
<id name="Id" type="long" column="HIERARCHY_ID" >
<generator class="hilo"/>
</id>
<property name="Name" type="string" />
<bag name="MemberCollection" cascade="all"
inverse="true" lazy="false" batch-size="100">
<key column="HIERARCHY_ID" />
<one-to-many class="NHibernateTest.Member"/>
</bag>
</class>
</hibernate-mapping>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateTest" namespace="NHibernateTest" default-
lazy="false">
<class name="NHibernateTest.Member, NHibernateTest"
table="MEMBERS" lazy="false">
<id name="Id" type="long" column="MEMBER_ID" >
<generator class="hilo"/>
</id>
<property name="Name" column="MEMBER_NAME"
type="string" />
<many-to-one name="ParentMember"
column="MEMBER_PARENT_ID" class="NHibernateTest.Member" />
<many-to-one name="ParentHierarchy"
column="HIERARCHY_ID" class="NHibernateTest.Hierarchy" />
<bag name="AttributeCollection" cascade="all"
inverse="true" lazy="false" batch-size="100">
<key column="MEMBER_ID" />
<one-to-many class="NHibernateTest.Attribute"/>
</bag>
<bag name="MemberCollection" cascade="all"
inverse="true" lazy="false" batch-size="100">
<key column="MEMBER_PARENT_ID" />
<one-to-many class="NHibernateTest.Member"/>
</bag>
</class>
</hibernate-mapping>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateTest" namespace="NHibernateTest" default-
lazy="false">
<class name="NHibernateTest.Attribute, NHibernateTest"
table="ATTRIBUTES" lazy="false">
<id name="Id" type="long" column="ATTRIBUTE_ID" >
<generator class="hilo"/>
</id>
<property name="Name" type="string" />
<many-to-one name="ParentMember" column="MEMBER_ID" /
>
</class>
</hibernate-mapping>
Scenario 1 - Persisting a deep hierarchy of transient objects
(generates lots of batch operations)
Given the following graph of transient objects:
Hierarchy (root)
+ Member1
+ Member2
+ Member3
+ Member4
+ Member5
+ Member6
All <bag> mappings have cascade="all". The adonet.batchsize is set to
100.
When I save my root object, Hierarchy, NHibernate generates 5 batch
statements, one for each level of siblings in the object graph:
insert Hierarchy
insert Member1
insert Member2, Member3
insert Member4, Member6
insert Member5
I get a different result when I change the Hierarchy - Member and
Member - Member associations to be cascade="none". In this case, I
programmatically create a flat list of Member objects while still
allowing Members to retain references to parents and children. When I
save my root object, Hierarchy, then loop through my flat list of
Members, saving one at a time, NHibernate generates only 2 batch
statements:
insert Hierarchy
insert Member1, Member2, Member3, Member4, Member5, Member 6
When I retrieve Hierarchy back from the database, NHibernate recreates
the hierarchy appropriately because my associations remain intact.
I know we're only talking about the difference between 2 and 5 round
trips, but you can imagine the performance when hierarchies are much
deeper and there are more Members. It is not uncommon to have
thousands of Members at a given level of a hierarchy.
My question is: Why is there a difference in the number of batches?
Why can't I leave the cascade="all" option set and perform the save in
one batch?
Scenario 2 - Retrieving a deep hierarchy of persistent objects
(requires multiple round trips)
I have a similar object graph to the one above, but with a new entity
called Attribute. Each Member can have 0..n Attributes.
Given the following graph of persisted objects:
Hierarchy (root)
+ Member1
- Attribute1
- Attribute2
- Attribute3
+ Member2
+ Member3
+ Member4
+ Member5
- Attribute4
- Attribute5
- Attribute6
+ Member6
All <bag> mappings have cascade="all". The adonet.batchsize is set to
100.
When I retrieve the root Hierarchy object, NHibernate generates the
following SQL statements (each is a separate round trip to the
database):
select Hierarchy
select Member left join Hierarchy = HierarchyId (Returns Member1)
select Member left join Hierarchy = HierarchyId and ParentMember is
(Member1) (Returns Member2, Member3)
select Member left join Hierarchy = HierarchyId and ParentMember is
(Member2 or Member3) (Returns Member4, Member6)
select Member left join Hierarchy = HierarchyId and ParentMember is
(Member4 or Member6) (Returns Member5)
select Member left join Hierarchy = HierarchyId and ParentMember is
(Member5) (Returns nothing...)
select Attribute where ParentMember is (Member1, Member2, Member3,
Member4, Member5, Member6) (Returns Attributes 1 to 6)
The issues with this are:
Lots of round trips.
The queries can get quite large if I have lots of
sibling members, which is a very common scenario (thousands of
Members).
I can reduce the number of SQL statements if I retrieve Members and
Attributes first by left joining Member with child Members and left
joining Member with Attributes.
For example:
session.CreateQuery("from Member m left join fetch m.MemberCollection
left join fetch m.AttributeCollection").List<Member>();
session.Get(typeof(Hierarchy), 1)
In this case, when I retrieve the root Hierarchy object, NHibernate
requires only 2 round trips to retrieve the complete object graph.
The issues to this alternative approach are:
The SQL query returns all Members and all Attributes
(which is great!), but because of the join and the arbitrary number of
Attributes on each Member, there could be a lot of data duplication.
It can take longer to retrieve large sets of data
where they are lots of associations.
I also tried retrieving all my Members and Attributes as separate
queries in a MultiQuery. NHibernate did send out two unique queries
as expected, but before the session flushed it issued some more
administrative queries to determine all the associations among
entities.
My question is: What's the optimal way to query this object graph? Is
a left join fetch required in order to preserve associations in an
eager fetch? I want to use NHibernate to manage the associations
between the different objects and I'd rather not have to
programmatically manage them myself.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
