• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help me in improving performance of prepared statement

 
Murthy Manchala
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.)


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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


In your code, I see executeBatch() all over so I suspect you aren't actually batching anything.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"murthy",
Please check your private messages.
-DOM
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic