help me in improving performance of prepared statement

[Murthy Manchala]

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();

[Jeanne Boyarsky]

Linga,
When you run Oracle explain, which of the statements does it say is taking the most time?

Is your Java code running on the same machine as the database? If not, you might have a tremendous amount of network traffic. A stored procedure or batching could help with that. (You say you tried batching, but I'm not seeing it in the code.)

((OraclePreparedStatement)pstmt).setExecuteBatch(30);

Why are you using an OraclePreparedStatement instead of the normal PreparedStatement API? What does this line of code do? It's non-standard, so I've never used it.

[Murthy Manchala]

Hi Jeane,

My java code and oracle are running on different machines.I am using oracle update batch instead of standard update batch, thats why only i casted the preparedstatement to oracle preparedstatement. If it is not correct can you please send me example code for batch.

Thank you...

[Jeanne Boyarsky]

Originally posted by linga murthy manchala:
If it is not correct can you please send me example code for batch.

It's not wrong. It's just not the most common way. People generally only use Oracle specific functions when no generic one exists. Using the Oracle version means people are less likely to be able to advise you. It looks to me that batching isn't occurring in your code snippet, but I can't be sure.

From Suns's tutorial, here's a a sample of the standard way:

PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)");
 
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();
 
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();
 
// submit the batch for execution
int[] updateCounts = stmt.executeBatch();

In your code, I see executeBatch() all over so I suspect you aren't actually batching anything.

[David O'Meara]

"murthy",
Please check your private messages.
-DOM