Reputation: 4973
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
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