CODEWITHSUNDEEP
javamysqlspringhibernatespring-data-jpa
Kyle Xie
Kyle Xie

Reputation: 31

Spring JPA Hibernate DeleteByColumnName behaves very inefficient

I was trying to use the Spring's CrudRepository work with Hibernate to delete rows by a non-primary-key column, using deleteByColumnName method. However, the actual executed query is very inefficient and too slow in practice.

Suppose I have two tables Project and Employee, and each employee is in charge of some projects, which implies that the Project table has a field employee_id. Now I would like to delete some projects by employee_id. I wrote something like

public interface ProjectRepository extends CrudRepository<Project, String> {
    @Transactional
    void deleteByEmployeeId(String employeeId);
}

What I am expecting is Hibernate will execute the following query for this method 

DELETE FROM Project
WHERE employee_id = ?

However, Hibernate executes it in a drastically slow way like

SELECT id FROM Project
WHERE employee_id = ?

Hibernate stores the above result in a list, and execute

DELETE FROM Project
WHERE id = ?

for N times... (it executes in batch though)

To address this inefficiency problem, I have to override the method by writing SQL directly, like

public interface ProjectRepository extends CrudRepository<Project, String> {
    @Query("DELETE FROM Project p where p.employee_id = ?1")
    @Modifying
    @Transactional
    void deleteByEmployeeId(String employeeId);
}

Then the behavior will be exactly the same as what I am expecting.

The performance is substantially distinct when I delete about 1k rows in a table containing around 500k entries. The first method will take 45 seconds to finish the deleting compared to the second methods taking only 250ms!

The reason I use Hibernate is taking advantage of its ORM strategy that avoids the use of SQL language directly, which is easy to maintain in the long run. At this point, is there anyone who know how to let Hibernate execute the deletion in the manner of my second method without directly writing the SQL? Is there something I am missing to optimize the Hibernate performance?

Thanks in advance!

Upvotes: 3

Views: 1769

Answers (1)

hfintegrale
hfintegrale

Reputation: 11

Here you can find a good explanation why Hibernate has this bad performace when deleting Project items Best Practices for Many-To-One and One-To-Many Association Mappings

Upvotes: 1

Related Questions