user3123690 user3123690 - 3 months ago 9
Java Question

Best strategy to delete a large entity in Hibernate

I have a parent Entity which has many chilren collection in a web application. What is the best stratety deleting this entity? I could just delete the parent and all children collection will be deleted as a result because of cascade. But I am afraid this would take long time and might cause table locking which will affect other users. Or I can remove each children and remove the parent entity at the end. What is the best way?
To remove both parent and children at once. I use this.

entityManger.remove(parent); -> one transaction

Or I can loop through each children collection and remove all children first and at the end remove the parent. This would split one large transaction into many smaller ones.


All the other answers doesn't seem to address your problem.

First of all, unless you are dealing with heavily used table or deletion in order or millions, you are not going to face any significant performance issue.

However, if you do have the above scenario, you can do several things (and you must choose between performance or ROW locking prevention)

1) Make sure your foreign key has index in it

2) Do a batch delete like you said. This will prevent locking but will slow the performance since the app needs to call several statements to DB. More info here

3) Use native SQL queries and do a batch delete

DECLARE @RowCount int
        DELETE TOP (10000) t1
        FROM table t1
        WHERE t1.FK_ID = yourFkId

        SET @RowCount = @@ROWCOUNT

        IF (@RowCount < 10000) BREAK

The most important thing is to test whether it is your bottleneck issue and avoid premature optimization.

Hope it helps