the simplest solution is instead of setting cascade delete, in the DAO/Repository, when deleting the father entity, use an HQL delete statement on the children where their FatherId = the deleted father.
On Fri, Aug 27, 2010 at 9:45 AM, Frans Bouma <[email protected]> wrote: > > there are 2 things I see that would cause this situation. > > 1. you are using the native generator. this usually means the database > > controls the PK instead of the application. the downside to this approach > is > > you loose the ability to batch CUD statements. I would strongly recommend > > switching to either guid.comb or hilo. > > > > 2. if you have the cascade attribute set for the key of your collections > > it's set to noaction, which means the database will do nothing. change > this > > to on-delete="cascade" and the database will delete them automatically > > (assuming the db has this feature). if the on-delete attribute does not > > exist, add it with the option to cascade. > > this should fix the problem. > > and assuming the entities aren't connected through multiple paths :) > > > if the database does not support cascades, then NH must manage the > deletes, > > in which case option 1 above becomes very important. without batching the > > entities will be removed one at a time. > > Doesn't NH support some kind of direct delete feature? I.e. delete > entities in bulk matching a given set of predicates? Which then results in > queries like DELETE FROM Foo WHERE ... > > IMHO that should make MattO's problem go away instantly. > > FB > > > > > On Aug 26, 6:50 pm, MattO <[email protected]> wrote: > > > Okay, here they are (shortened). Basically there is one ContentCode > > > (parent) to usually 300 to 400 ContentCodeDetails. > > > > > > Then for every user in the system that has access to the content code > > > details there could be up to 2000 to 4000 records for EACH user in the > > > ContentCodeAccess table. > > > > > > The problem is if I delete a parent, it deletes it with one delete > > > statement, then it deletes the 300 to 400 ContentCodeDetails with 300 > > > to 400 DELETE statements, and finally it deletes the ContentCodeAccess > > > records with 2000 to 4000 delete statements. > > > > > > In my old ADO .NET code I did this in less than a second by running 3 > > > delete statements. Now it executes around 2500 to 4000 delete > > > statements when I delete a parent record and takes 40 to 60 seconds. > > > > > > Here is the Parent (ContentCode): > > > > > > <class name="ItemBankCoreNH.BusinessObjects.ContentCode, > > > ItemBankCoreNH" table="[dbo].[ContentCodes]" > > > optimistic-lock="version" dynamic-update="true" > > > lazy="true"> > > > <id name="Id" column="[ContentCodeID]"> > > > <generator class="native" /> > > > </id> > > > <version name="TS" generated="always" unsaved-value="null" > > > type="BinaryBlob"> > > > <!-- Generated="always" is to have database handle > > > generation of timestamp --> > > > <column name="TS" not-null="false" sql-type="timestamp"/> > > > </version> > > > ... More properties.... > > > <bag name="ExamTypes" lazy="true" cascade="none"> > > > <key column="[Type]" property-ref="ExamType" not- > > > null="true" on-delete="noaction" update="false"></key> > > > <one-to-many class="ExamType" /> > > > </bag> > > > <bag name="ContentCodeDetails" inverse="true" cascade="all- > > > delete-orphan" lazy="true"> > > > <key not-null="true"> > > > <column name="[ContentCodeID]" sql-type="int" /> > > > </key> > > > <one-to-many class="ContentCodeDetail" /> > > > </bag> > > > </class> > > > > > > Here is the Child (ContentCodeDetails): > > > > > > <class name="ItemBankCoreNH.BusinessObjects.ContentCodeDetail, > > > ItemBankCoreNH" table="[dbo].[ContentCodeDetail]" > > > optimistic-lock="version" dynamic-update="true" > > > lazy="true"> > > > <id name="Id" column="[ContentID]"> > > > <generator class="native" /> > > > </id> > > > <version name="TS" generated="always" unsaved-value="null" > > > type="BinaryBlob"> > > > <!-- Generated="always" is to have database handle > > > generation of timestamp --> > > > <column name="TS" not-null="false" sql-type="timestamp"/> > > > </version> > > > <property name="ContentCodeID" column="[ContentCodeID]" /> > > > .... More Properties... > > > <many-to-one name="ContentCodeParent" class="ContentCode" > > > insert="false" update="false" fetch="join"> > > > <column name="[ContentCodeID]" sql-type="int" /> > > > </many-to-one> > > > <bag name="ContentCodeAccessDetails" cascade="all-delete- > > > orphan" lazy="true"> > > > <key not-null="true"> > > > <column name="[ContentID]" sql-type="int" /> > > > </key> > > > <one-to-many class="ContentCodeAccess" /> > > > </bag> > > > </class> > > > > > > And finally the table with the most records (ContentCodeAccess): > > > > > > <class name="ItemBankCoreNH.BusinessObjects.ContentCodeAccess, > > > ItemBankCoreNH" table="[dbo].[ContentCodeAccess]" > > > optimistic-lock="version" dynamic-update="true" > > > lazy="true"> > > > <composite-id> > > > <key-property name="ContentID" column="[ContentID]" /> > > > <key-property name="DomainUser" column="[DomainUser]" /> > > > <key-property name="ExamType" column="[ExamType]" /> > > > <key-property name="ContentCodeTypeID" > > > column="[ContentCodeTypeID]" /> > > > </composite-id> > > > <version name="TS" generated="always" unsaved-value="null" > > > type="BinaryBlob"> > > > <!-- Generated="always" is to have database handle > > > generation of timestamp --> > > > <column name="TS" not-null="false" sql-type="timestamp"/> > > > </version> > > > </class> > > > > > > On Aug 26, 3:57 pm, Jason Meckley <[email protected]> wrote: > > > > > > > inverse means the root entity is not responsible for the id of child. > > > > the child is itself an entity and will manage it's own POID. > > > > > > > What do you're mappings look like? I would assume you have > > > > cascade="all" or all-delete-orphan. The strategies will use NH to > > > > manage the deletion rather than letting the database delete child > > > > relations through FK cascades. > > > > > > > if you set cascade="save-update" with the key as on-delete="cascade" > > > > the database should manage the deletion of related entities, if it > > > > supports cascade deletes. if it doesn't you can also look into > > > > batching ADO.Net calls. This may not work depending on your POID > > > > strategy. > > > > > > > On Aug 26, 4:22 pm, MattO <[email protected]> wrote: > > > > > > > > How do you overcome the issue with nHibernate where collections > > > > > that are mapped with inverse="true" will be issued a delete > > > > > statement for every object in the collection? > > > > > > > > Right now this has added about 50 seconds to a single delete > > > > > operation which normally only took less than a second to perform > > > > > in straight ADO .NET by performing a delete based on the primary > > > > > key of the parent. > > > > > > > > According to the nHibernate reference manual section 17.5.4 (one > > > > > shot delete), by discarding (dereferencing) the original > > > > > collection and returning a newly instantiated collection with all > > > > > the current elements. > > > > > > > > Can someone please tell me what they mean with a code sample if > > > > > this also applys to inverse="true" collections, or is there some > > > > > other option other then manually handling deletes for these types > > > > > of collections yourself?- Hide quoted text - > > > > > > > - Show quoted text - > > > > > > > > > > -- > > 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]<nhusers%[email protected]> > . > > For more options, visit this group at > > http://groups.google.com/group/nhusers?hl=en. > > -- > 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]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- 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.
