oh. actually, won't it actually be better that nhibernate will delete where father.id = :fatherId instead of each child getting a delete statement, when cascade is set to delete?
On Fri, Aug 27, 2010 at 5:36 PM, MattO <[email protected]> wrote: > Okay, I see what on-delete cascade does at the database level. This > assumes you put on cascading delete foreign keys on the database level > as well. Guess I'll have to atleast make that change in order for > this to work (which I can do and is fairly minor). > > On Aug 27, 9:32 am, MattO <[email protected]> wrote: > > Also, I found the documentation on the on-delete option that appears > > to be missing from the nHibernate documentation here: > http://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html > > > > It is only valid on <key> nodes though, not sure if you can add on- > > delete to key-property tags on a composite key. I'm guessing not, so > > I may have to make some database changes and code changes. > > > > On Aug 27, 2:54 am, nadav s <[email protected]> wrote: > > > > > > > > > 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]> > <nhusers%[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]> > <nhusers%[email protected]<nhusers%[email protected]> > > > > > > . > > > > For more options, visit this group at > > > > ... > > > > read more »- 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]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
