Hibernate version:
3.3.1.GA
Spring version:
2.5.4
DB
postgresql 8.2.4
im planing to do a monthly update on some tables. there are several 100.000 rows to be updated and im using the code below. the first 10.000 rows update in about 5 minutes (including data-export from a datasource using pagination), but after that the update slows down doing only 1 or 2 updates per second!
doImport is called with 1000 records per call, so each transaction 1000 updates should happen.
the database isnt busy at all and there is also enough memory.
debugging my application i assured that only q.executeUpdate(); takes a lot of time to execute allthough the database is just waiting for some work..
any suggestions where to search for the bottleneck?
Code:
@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
public void doImport(List<Permission> permission, String source) {
for (Permission p: permissions) {
permissionEmailDao.updatePermissions p.costumernumber, p.email, p.newsletter, source;
count ++;
if (count > 20) {
this.m_sf.currentSession.flush();
this.m_sf.currentSession.clear();
count = 0;
}
}
}
and the dao:
Code:
private String subquery = "select id from "+ persistentClass.getName() +" where person.costumer_number = :thenumber";
private String update = "update " + persistentClass.getName() +
" set permissionStandAloneEmailFlag = :v1 , " +
"permissionNewsletterFlag = :v2, " +
"source = :dsd " +
"where id in ( " + subquery+ " )" ;
public void updatePermissions(String costumerNumber, Integer permissionStandAloneEmailFlag, Integer permissionNewsletterFlag, DataSourceDescriptor dsd) {
q = this.getSession().createQuery(update);
q.setInteger("v1", permissionStandAloneEmailFlag);
q.setInteger("v2", permissionNewsletterFlag);
q.setString("thenumber", costumerNumber);
q.setParameter("dsd", dsd);
q.executeUpdate();
}
the generated sql:
Code:
update dwh_test.PERMISSION_EMAIL set PERMISSION_STANDALONE_EMAIL_FLAG=?, PERMISSION_NEWSLETTER_FLAG=?, SOURCE=? where id in (select permission1_.id from dwh_test.PERMISSION_EMAIL permission1_, dwh_test.PERSON person2_ where permission1_.PERSON=person2_.id and person2_.COSTUMER_NUMBER=?)
Login
FAQ
Search
