Delete many Objects using cascade is very slow

klemensl · **Joined:** Wed Dec 13, 2006 10:39 am **Posts:** 26

Hibernate version: 3.2.2

Name and version of the database you are using: MSSQL Server 2000 8.00.194

hi,

i want to delete _one_ object which is a parent of an other object which is parent of another object which is.... (4 levels, ~7000 objects)

RootObject
-X1
--X11
---X111
---X112
--X12
---X121
---X122
-X2
--...
--X13

i just tell hibernate to delete the object "RootObject" -> session.delete(RootObject). RootObject deletes all child objects with cascade=...,delete and every child deletes its child objects with cascade=...,delete and so on.
first many updates are executed to eleminate the parent/child foreign key constraints, then for every object a single delete statement is executed -> in my case about 7000 objects means about 7000 sql statements and the currentTransaction.commit() lasts ~800 seconds.

how can i improve the performance without loosing the advantage of using cascade=delete?

thx in advane

plasmafire · **Posted:** Tue Dec 18, 2007 12:32 pm

Sorry to say this, but apart from optimizing the database side of it, all you can do is perhaps batch the deletes.

klemensl · **Joined:** Wed Dec 13, 2006 10:39 am **Posts:** 26

plasmafire wrote:

Sorry to say this, but apart from optimizing the database side of it, all you can do is perhaps batch the deletes.

but as far as i understand batching, i have to delete the objects "manually" and flush the session after ~100 deletes and so i am not able to use the casacade feature. am i rirght?

plasmafire · **Posted:** Tue Dec 18, 2007 2:04 pm

cascade deletes. Does your code look like this?

Code:

<set name="children" inverse="true" cascade="all">
  <key name="PARENT_ID" on-delete="cascade"/>
  <one-to-many class="Child"/>
</set>

Just check if the elements are getting instantiated before deletion, in that case..you are doing something wrong.

klemensl · **Joined:** Wed Dec 13, 2006 10:39 am **Posts:** 26

plasmafire wrote:

cascade deletes. Does your code look like this?

Code:

<set name="children" inverse="true" cascade="all">
  <key name="PARENT_ID" on-delete="cascade"/>
  <one-to-many class="Child"/>
</set>

Just check if the elements are getting instantiated before deletion, in that case..you are doing something wrong.

my mapping looks like

Code:

<parentclass testRun>
 <list name="testResults" lazy="true" inverse="false" cascade="persist,evict,delete,replicate,lock,save-update">

            <key column="testrun_id">
            </key>

            <index column="sequence"/>

            <one-to-many
                  class="com.seqis.kltm.model.db.project.TestResult"
            />

        </list>
</parentclass>

<childclass TestResult>
        <many-to-one
            name="testRun"
            class="com.seqis.kltm.model.db.project.TestRun"
            cascade="none"
            update="false"
            insert="false"
            column="testrun_id"
       lazy="false"
        />
</childclass>

i will look, if the elemets get instantiated before deletion.

plasmafire · **Posted:** Wed Dec 19, 2007 8:15 am

Meantime, try adding

Code:

on-delete="cascade"/>

klemensl · **Joined:** Wed Dec 13, 2006 10:39 am **Posts:** 26

plasmafire wrote:

Meantime, try adding

Code:

on-delete="cascade"/>

i read about on-delete=cascade today and already tried it, but it is only allowed with inverse=true, my mappings are inverse=false because they are indexed.

plasmafire · **Posted:** Wed Dec 19, 2007 9:40 am

In that case, hibernate is behaving correctly, and it should indeed check before deleting, if inverse <> true.
Please look at optimising the database queries

klemensl · **Joined:** Wed Dec 13, 2006 10:39 am **Posts:** 26

plasmafire wrote:

In that case, hibernate is behaving correctly, and it should indeed check before deleting, if inverse <> true.
Please look at optimising the database queries

why is hibernate restricting on-delete=cascade to inverse=true assocations?

plasmafire · **Posted:** Wed Dec 19, 2007 11:03 am

http://www.hibernate.org/hib_docs/v3/reference/en/html/collections.html#collections-bidirectional

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Hi,
Did you figure out a way to delete with cascade?