• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

help me in improving performance of prepared statement

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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();
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"murthy",
Please check your private messages.
-DOM
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic