Java Stored Procedure performance issue...

cnoel · Jul 16, 2001

Hi all..
I have recently developed an update procedure for our imaging system using JDBC. The procedure performed updates on approx 13,000 records, and took just over 13 hours. This equates to 1 transactions per second... which is not acceptable for future use. My initial suspicions target the db logging scheme, but thought i would touch base here for additional ideas. The code follows below. The app runs well until it hits the routine to perform the updates (see 'update the nines'), then it slows considerably. We do have roll forward recovery enabled with 1 meg log file size.
Any suggestions would be appreciated.
Thanks
Curtis

/**
* JDBC Stored Procedure DB2ADMIN.JNinesUpdate
*/

import java.sql.*; // JDBC classes

public class JNinesUpdate
{
public static void JNinesUpdate () throws SQLException, Exception
{
// Get connection to the database

Connection con = DriverManager.getConnection("jdbc:default:connection&quot

;

// Variable declarations

PreparedStatement delstmt = null;
PreparedStatement ninesstmt = null;
PreparedStatement matchesstmt = null;
PreparedStatement updatestmt = null;
PreparedStatement sortstmt = null;
PreparedStatement insertstmt = null;
PreparedStatement cycle = null;

ResultSet rs = null;
ResultSet ud = null;
int x = 0;

String tablename = "";
String tablenumber = "";
String ninessql = "";
String deletesql="";
String matchessql="";
String updatesql="";
String sortsql = "";
String insertsql = "";
String cyclesql = "";

// housekeeping
deletesql = "DELETE FROM DB2ADMIN.TPRESORT;";
delstmt = con.prepareStatement( deletesql );
delstmt.executeUpdate();

deletesql = "DELETE FROM DB2ADMIN.TNINES;";
delstmt = con.prepareStatement( deletesql );
delstmt.executeUpdate();

deletesql = "DELETE FROM DB2ADMIN.TMATCHES;";
delstmt = con.prepareStatement( deletesql );
delstmt.executeUpdate();

// find the nines..

for (x=59; x<=106; x++)
{
tablenumber = String.valueOf(x);

if (x!=66 && x!=74 && x!=82 && x!=90 && x!=98 && x!=102)
{
if (x<100)
{
tablename = "DB2ADMIN.AVT000" + tablenumber;
}
else
{
tablename = "DB2ADMIN.AVT00" + tablenumber;
};

ninessql = "INSERT INTO DB2ADMIN.TNINES (DCN,ITEMID,LOCATION) SELECT "
+ tablename + ".ATTRIBUTE00120, "
+ tablename + ".ITEMID, "
+ tablenumber
+ " FROM " + tablename
+ " WHERE "
+ tablename + ".ATTRIBUTE00114 = '9999999999';";

ninesstmt = con.prepareStatement( ninessql );
ninesstmt.executeUpdate();
}
}

// find the matches for nines...

for (x=59; x<=106; x++)
{
tablenumber = String.valueOf(x);
tablenumber.trim();

if (x!=66 && x!=74 && x!=82 && x!=90 && x!=98 && x!=102)
{
if (x<100)
{
tablename = "DB2ADMIN.AVT000" + tablenumber;
}
else
{
tablename = "DB2ADMIN.AVT00" + tablenumber;
};

matchessql = "INSERT INTO DB2ADMIN.TPRESORT (DCN,LOCATION,ITEMID,POLICYNUMBER,LASTUPDATED) "
+ "SELECT DB2ADMIN.TNINES.DCN, "
+ "DB2ADMIN.TNINES.LOCATION, "
+ "DB2ADMIN.TNINES.ITEMID, "
+ tablename + ".ATTRIBUTE00114, "
+ tablename + ".ATTRIBUTE00119 "
+ "FROM DB2ADMIN.TNINES "
+ "INNER JOIN " + tablename
+ " ON DB2ADMIN.TNINES.DCN = " + tablename + ".ATTRIBUTE00120 "
+ "WHERE (((" + tablename + ".ATTRIBUTE00114) <> '9999999999'));";

matchesstmt = con.prepareStatement( matchessql );
matchesstmt.executeUpdate();
}
}
// sort the matches and write them to the sorted table;

sortsql = "SELECT DCN, "
+ "POLICYNUMBER, "
+ "LOCATION, "
+ "LASTUPDATED, "
+ "ITEMID "
+ "FROM DB2ADMIN.TPRESORT "
+ "ORDER BY LASTUPDATED ASC;";

sortstmt = con.prepareStatement( sortsql );
rs = sortstmt.executeQuery();

while (rs.next())
{

insertsql = "INSERT INTO DB2ADMIN.TMATCHES (DCN,POLICYNUMBER,LOCATION,LASTUPDATED,ITEMID) "
+ "VALUES ('" + rs.getString(1)
+ "','" + rs.getString(2)
+ "'," + rs.getString(3)
+ ",'" +rs.getString(4)
+ "','" +rs.getString(5)
+ "');";

insertstmt = con.prepareStatement ( insertsql );
insertstmt.executeUpdate();

}

//update the nines

for (x=59; x<=106; x++)
{
tablenumber = String.valueOf(x);

if (x!=66 && x!=74 && x!=82 && x!=90 && x!=98 && x!=102)
{
if (x<100)
{
tablename = "DB2ADMIN.AVT000" + tablenumber;
}
else
{
tablename = "DB2ADMIN.AVT00" + tablenumber;
};

cyclesql = "SELECT FROM DB2ADMIN.TMATCHES WHERE LOCATION = " + x;

cycle = con.prepareStatement( sortsql );
ud = sortstmt.executeQuery();

while (ud.next())
{

updatesql = "UPDATE " + tablename
+ " SET (" + tablename + ".ATTRIBUTE00114, "
+ tablename + ".ATTRIBUTE00119) = "
+ "('" + ud.getString(2) +"', CURRENT DATE) "
+ "WHERE (((" + tablename + ".ATTRIBUTE00114)='9999999999')"
+ "AND ((" + tablename + ".ATTRIBUTE00120)='" + ud.getString(1) + "'));";

updatestmt = con.prepareStatement ( updatesql );
updatestmt.executeUpdate();

}

}
}

// endgame

if (con != null) con.close();

}
}

Justyn · Jul 18, 2001

May I ask you do u schedule the stored procudure to run daily?
If so, can you tell me how to schedule java stored procedure please? I don't know how to do this and I urgently need to do this.....
Thanks a lot!

cnoel · Jul 20, 2001

with a tad bit of embarassment, I have located the flaw in execution. Being a little quick on the cut & paste caused me to miss a variable change

cyclesql = "SELECT FROM DB2ADMIN.TMATCHES WHERE LOCATION = " + x;

cycle = con.prepareStatement( sortsql );
ud = sortstmt.executeQuery();

while (ud.next())

cycle = con.prepareStatement( sortsql );

should have been

cycle = con.prepareStatement( cyclesql );

this caused the proc to loop through the entire recordset intstead of a filtered verion. probably added 50,000 additional i/o actions, which might have just a little something to do with the miserable performance.

Justyn,
The jobs are set up through the script center, where you can define and schedule your scripts, I have not explicity performed this with stored procedures, only backups.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Java Stored Procedure performance issue...

cnoel

Programmer

Justyn

Instructor

cnoel

Programmer

Similar threads

Part and Inventory Search

Sponsor