3.1rc2:
DB2 V7:
I have a simple scenario where I am performing a bulk delete operation via a DAO 500 times (for different business keys).
Using an HQL bulk delete it takes 2000 times longer to execute the queries than using JDBC (146 seconds compared to 0.08 seconds).
The HQL way looks like
Code:
public class AssetLifeHistoryDAO extends AbstractDAO {
private Query q = null;
public void deleteSortieRecords(String serialNumber, String equipmentType) throws Exception {
if ( q == null ){
Session s = BdsConnection.currentSession();
q = s.createQuery("delete from lits.bds.ess.AssetLifeHistory"+
" where "+
" id.assetSerialNo = :serialNo and "+
" id.equipmentType = :equipmentType and "+
" sortieFlag = 'Y' and"+
" id.lmu <> :lmu");
}
q.setString("serialNo", serialNumber);
q.setString("equipmentType", equipmentType);
q.setString("lmu", "FLYING-HRS DEC MINS");
q.executeUpdate();
}
}
and the alternative JDBC method (using the hibernate created JDBC connection)
Code:
public class AssetLifeHistoryDAO extends AbstractDAO {
private PreparedStatement p = null;
public void deleteSortieRecords(String serialNumber, String equipmentType) throws Exception {
if ( p == null ){
Session s = BdsConnection.currentSession();
String sql = "DELETE FROM lits.t_asset_life_his where mach_no=? and machcl_cde=? and mruom_cde <> ? and srt_flg='Y'";
Connection c = s.connection();
p = c.prepareCall(sql);
}
p.setString(1, serialNumber);
p.setString(2, equipmentType);
p.setString(3, "FLYING-HRS DEC MINS");
p.execute();
}
}
With show_sql set to true the delete queries sent to the database are identical. I guess Hibernate is doing something else too ?
Any help would be appreciated (for now I will use the JDBC option).
Login
FAQ
Search
