Thanks Ricardo, I hadn't stumbled upon that particular StackOverflow post
in my own search; however the proposed solution unfortunately doesn't
appear to be working out for me.
What I have done is added BatchSize(500) to the HasMany mapping in my
UserMap in addition to setting a BatchSize when configuring NHIbernate
Fluently:
public UserMap() {
Id(x => x.Id);
Map(x => x.Description);
HasMany(x => x.Attributes)
.BatchSize(500)
.Inverse()
.Cascade.AllDeleteOrphan();
}
With this setup, I'm still getting multiple individual deletes:
DELETE FROM [Attribute] WHERE Id =
46f01d8c-2f3f-4bee-917c-a38000a53c54 /* @p0_0 */
DELETE FROM [UserAttribute] WHERE Id =
0ca093f6-bc81-44e8-869b-a38000a53c54 /* @p0_0 */
DELETE FROM [Attribute] WHERE Id =
8645a8c3-a840-4724-9b62-a38000a713f3 /* @p0_0 */
DELETE FROM [UserAttribute] WHERE Id =
07fafa9b-b9de-434f-8a93-a38000a713f3 /* @p0_0 */
...
I have also tried setting the BatchSize at the top of each Map, but it
doesn't appear to make a difference..?
Inserts and Updates are batched together, and if I change the Attribute
Mapping in UserAttributeMap to Cascade.SaveUpdate(), it will batch together
deletes of the UserAttribute objects but as soon as I try to cascade
through to the Attributes I'll get this N+1 behavior.
If this is indeed a bug, I will raise a call for it, I assume I'm not the
first person trying to do this.
On Wednesday, August 6, 2014 10:56:27 PM UTC+1, Ricardo Peres wrote:
>
> Apparently, it's a limitation of NHibernate:
> http://stackoverflow.com/questions/7253749/nhibernate-cascade-alldeleteorphan-why-is-nh-deleting-each-collection-item-indi
> Indeed, IMO, this should have been fixed a long time ago, it doesn't seem
> that difficult to do. Maybe you can raise an issue in JIRA?
>
> RP
>
> On Wednesday, August 6, 2014 3:02:47 PM UTC+1, Stevie Shannon wrote:
>>
>> I had actually attempted to fall-back to Clearing the collection prior to
>> doing the Delete, however, in this scenario, calling Clear() on the User's
>> Attribute collection will also generate an individual DELETE for each
>> UserAttribute and each Attribute - again the N+1 problem.
>>
>> On Wednesday, August 6, 2014 2:52:57 PM UTC+1, Ricardo Peres wrote:
>>>
>>> Not exactly what you want, but if you call Clear() on a collection, it
>>> should delete all records with a single DELETE statement.
>>>
>>> RP
>>>
>>> On Tuesday, August 5, 2014 5:02:37 PM UTC+1, Stevie Shannon wrote:
>>>>
>>>> I've spent some time on this problem scouring resources online and a
>>>> couple of books I have on NH; but I'm struggling to get to the bottom of
>>>> it.
>>>>
>>>> Basically: I'm finding that cascading deletes is sending SQL with many
>>>> individual deletes, i.e. the N+1 problem.
>>>>
>>>> I have three tables: User, UserAttribute and Attribute. Each User has
>>>> many UserAttributes, each UserAttribute has one Attribute - represented by
>>>> the following Maps:
>>>>
>>>> public UserMap() {
>>>> Id(x => x.Id);
>>>>
>>>> Map(x => x.Description);
>>>>
>>>> HasMany(x => x.Attributes)
>>>> .Inverse()
>>>> .Cascade.AllDeleteOrphan();
>>>> }
>>>>
>>>> public UserAttributeMap() {
>>>> Id(x => x.Id);
>>>>
>>>> References(x => x.User)
>>>> .Not.Nullable();
>>>>
>>>> References(x => x.Attribute)
>>>> .Not.Nullable()
>>>> .Cascade.All();
>>>> }
>>>>
>>>> public AttributeMap() {
>>>> Id(x => x.Id);
>>>>
>>>> Map(x => x.Name)
>>>> .Unique();
>>>> }
>>>>
>>>> The problem I'm experiencing is that when I delete a User entity, the
>>>> cascade generates an individual delete for each UserAttribute and each
>>>> Attribute; which is a potentially large performance problem (A User could
>>>> typically have hundreds of Atributes).
>>>>
>>>> In the above test scenario my schema is then generated using FluentNH,
>>>> I have an IdConvention setting all Ids to GuidComb and In addition to
>>>> this,
>>>> I have a repository that I go through to access the Session.SaveOrUpdate,
>>>> Session.Delete, etc - each of which is wrapped in a Transaction.
>>>>
>>>> Is there something obviously wrong with the mapping that would be
>>>> triggering the N+1 problem here? Is there anything I can do to stop it? Is
>>>> this crazy talk?
>>>>
>>>> -------
>>>>
>>>> As a side note, my NHibernate configuration already includes a large
>>>> batch size, but as far as I'm aware batching won't wrap up something like
>>>> this? They certainly don't appear to be batched when I profile them.
>>>>
>>>> NHibernate v3.3.1.4000
>>>> FluentNHibernate v 1.4.0.0
>>>>
>>>> UserTable:
>>>> Id
>>>> Description
>>>>
>>>> UserAttributeTable:
>>>> Id
>>>> User_id (FK)
>>>> Attribute_id (FK)
>>>>
>>>> AttributeTable:
>>>> Id
>>>> Name
>>>>
>>>>
>>>>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
