aspose file tools*
The moose likes JDBC and the fly likes help me in  improving performance of prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "help me in  improving performance of prepared statement" Watch "help me in  improving performance of prepared statement" New topic
Author

help me in improving performance of prepared statement

Murthy Manchala
Greenhorn

Joined: Aug 20, 2008
Posts: 21
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

Joined: May 26, 2003
Posts: 30758
    
156

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Murthy Manchala
Greenhorn

Joined: Aug 20, 2008
Posts: 21
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

Joined: May 26, 2003
Posts: 30758
    
156

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

Joined: Mar 06, 2001
Posts: 13459

"murthy",
Please check your private messages.
-DOM
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: help me in improving performance of prepared statement