Hibernate Tip: What is the fastest option to delete 100 database records

By Thorben Janssen

Query

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

In one of my use cases, I need to remove a few hundred entities, and I can see in my tests that this gets really slow. Is there any better option than the EntityManager.remove method?

Solution:

JPA and Hibernate provide 2 general options to remove entities.

The most common one is the remove method of the EntityManager. It is easy to use but also very inefficient if you need to remove a list of entities.

For each of the entities you want to delete, Hibernate has to load the entity, perform the lifecycle transition to removed and trigger the SQL DELETE operation. That requires at least 1 query to load all entities and an additional SQL DELETE statement for each of them. So, when you use this approach to remove 100 entities, Hibernate has to perform at least 101 SQL statements.

It’s often better to remove such a list of entities with a JPQL query. Similar to SQL, you can define a JPQL DELETE operation that removes all entities with just 1 statement.

// Write all pending changes to the DB and clear persistence context
em.flush();
em.clear();
 
// Remove all entities referenced in the List ids variable
Query query = em.createQuery("DELETE Author a WHERE id IN (:ids)");
query.setParameter("ids", ids);
query.executeUpdate();

But be careful, this approach has a drawback. Hibernate doesn’t load the entities and doesn’t perform the lifecycle state transition to removed. That provides substantial performance benefits, but it also prevents Hibernate from updating its caches, incl. the 1st level cache, and from triggering any lifecycle events or listeners.

You should always call the flush and clear method on your EntityManager to detach all entities from the current persistence context and to clear the 1st level cache before you perform a JPQL UPDATE or DELETE statement. I get into more details on that in Hibernate Tips: How to remove entities from the persistence context before doing bulk operations.

Learn more:

You can use the same approach with Native queries or the Criteria API.

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!



Tags

Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Reply

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. Hello, I`m traying to delete a bulk of data, using a nameQuery just like you, but few elements have contraint violations and hibernate generate an entire rollback. it`s possible avoid the rollback? ie: 100 records for delete, 3 have constraints, hibernate generate a rollback for 100, but I need rollback only for 3 and delete 97 elements. It’s possible ? Right now I created a bad solutions, I used a for cycle and execute 100 sql queries.

    1. Hi,
      If the database finds a constraint violation, Hibernate rolls back the database transaction. That’s a typical behavior commonly used by persistence frameworks.
      With Hibernate, the only way to prevent that would be to start a transaction for each record you want to delete. But that would have severe performance impacts.
      The better option is to check in advance if any constraints prevent the removal of a record. You should also execute the queries in the correct order to avoid any foreign key constraint violations.

      Regards,
      Thorben

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}