CODEWITHSUNDEEP
javahibernatehqlbulk
Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

How to remove records from table in bulk with hibernate hql?

I am trying delete records in bulk from database table Student.

Everything is running fine but my question is :

Is there any limitation when my list (refer to query: studentIdList) size is greater than 1000000 in given piece of code? Do I need to do anything extra in such situation?

String hql = "delete from Student where id in (:studentIdList)";
session.createQuery(hql).setParameterList("studentIdList",studentIdList).executeUpdate();
session.flush();

Upvotes: 1

Views: 704

Answers (1)

Ankur Singhal
Ankur Singhal

Reputation: 26077

There are few things to be considered.

1.) how cache will behave if configured.

2.) For 1000000 records, definitely load test needs to be done. Are there any changes of OOM error.

You can try with above HQL and Hibernate Batch and then measure and come up with the statistics. Blindly telling the stats is impossible.

Also instead of using IN operator, how about the performance of using == in loop.

WHERE id = 1;

Is transformed to a simple equality filter.

WHERE id IN (1);

Is transformed into an array match of:

WHERE id = ANY(ARRAY[1]);

I will suggest you to please try, it will be an interesting exercise for you.

After reading more and experimenting, Batch will internally make a final query with IN operator only.SO using batch will add extra process in between, my thought using HQL with IN for delete operation will be good to go.

Upvotes: 2

Related Questions