posted 15 years ago
Hi everybody
I am trying to insert/update data in oracle database,retreving data from sql database. I am using prepared statements for update the records. its taking 1 hour to update the records about 8000. then i tried oracle batch update but still it is taking same time. below is my code
Please help me in improving the performance issue............
Thanks in advance
............................................................................
con1.setAutoCommit(false);
pstmt = con1.prepareStatement("insert into SA_ASSET_INVENTORY(ASSET_ID,ASSET_DESCRIPTION,ASSET_FACILITY,ASSET_DEPARTMENT,ASSET_LOCATION,ASSET_ADDRESS1,ASSET_STATE,ASSET_ZIP,ASSET_STATUS,DOMAIN_KEY,ELEMENT_KEY,ASSET_LAST_UPDATE,ASSET_TYPE,ASSET_UOM,ASSET_COUNT) values(?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?)");
((OraclePreparedStatement)pstmt).setExecuteBatch(30);
pstmt2 = con1.prepareStatement("select count(*) from SA_ASSET_INVENTORY where ASSET_ID = ? and DOMAIN_KEY = '2' and ELEMENT_KEY = '3'");
pstmt1 = con1.prepareStatement("delete from SA_ASSET_INVENTORY where ASSET_ID = ? and DOMAIN_KEY = '2' and ELEMENT_KEY = '3'");
((OraclePreparedStatement)pstmt1).setExecuteBatch(30);while (res.next()) {
pstmt2.setInt(1, res.getInt("Equipmentid"));
ResultSet count = pstmt2.executeQuery();
int count1 = 0;
while (count.next()) {
count1 = count.getInt(1);
System.out.println("count1 value = "+count1);
}
Date dat = res.getDate("ModifiedDate");
Calendar c1 = Calendar.getInstance();
Calendar c2 = Calendar.getInstance();
c1.set(2008, 04, 8);
c2.setTime(dat);
if (count1 == 0) {
System.out.println("Inserting rows....");
pstmt.setInt(1, res.getInt("Equipmentid"));
pstmt.setString(2, res.getString("EquipmentDescription"));
pstmt.setString(3, res.getString("FacilityName"));
pstmt.setString(4, res.getString("DepartmentName"));
pstmt.setString(5, res.getString("Location"));
pstmt.setString(6, res.getString("addressline1")+ ", "
+ res.getString("addressline2"));
pstmt.setString(7, res.getString("Abbreviation"));
pstmt.setInt(8, res.getInt("zipcode1")
+ res.getInt("zipcode2"));
pstmt.setString(9, "In Service");
pstmt.setInt(10, 2);
pstmt.setInt(11, 3);
pstmt.setString(12, res.getString("EquipmentTypeName"));
pstmt.setString(13, "EA");
pstmt.setInt(14,1);
pstmt.executeUpdate();
//((OraclePreparedStatement)pstmt).sendBatch();
} else {
if (c2.after(c1)) {
System.out.println("Updates are available....delete the row...and insert it"+ new Date());
//Deleting the row
sb.append(res.getInt("Equipmentid")+"|");
pstmt1.setInt(1, res.getInt("Equipmentid"));
pstmt1.executeUpdate();
sd = new Date().getTime();
System.out.println("deleted...inserting time"+sd);
//Inserting the row
pstmt.setInt(1, res.getInt("Equipmentid"));
pstmt.setString(2, res
.getString("EquipmentDescription"));
pstmt.setString(3, res.getString("FacilityName"));
pstmt.setString(4, res.getString("DepartmentName"));
pstmt.setString(5, res.getString("Location"));
pstmt.setString(6, res.getString("addressline1") + ", "
+ res.getString("addressline2"));
pstmt.setString(7, res.getString("Abbreviation"));
pstmt.setInt(8, res.getInt("zipcode1")
+ res.getInt("zipcode2"));
pstmt.setString(9, "In Service");
pstmt.setInt(10, 2);
pstmt.setInt(11, 3);
pstmt.setString(12, res.getString("EquipmentTypeName"));
pstmt.setString(13, "EA");
pstmt.setInt(14,1);
pstmt.executeUpdate();
} else{
log.info("No updates....");
System.out.println("No updates...");
}
}
con1.commit();
con1.setAutoCommit(true);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
con1.rollback();
e.printStackTrace();