Java Database Connectivity (JDBC) - Optimising JDBC Performance
I am newbie in JDBC and i am having a performance issue in deleting some data from tables,
this is the code block which i am using for selecting a perticular field from one table and than i go to other tables and delete data related to a perticular record
public void deleteRecord()
{
Statement stmt = null;
PreparedStatement qld_cal_seq = null;
PreparedStatement neg_fr_sl = null;
PreparedStatement cat_xpr = null;
PreparedStatement non_cmb_ru = null;
int commitlevel = 100 ;
ResultSet rs = null;
try
{
int qld=0;
int neg=0;
int cat=0;
int non=0;
stmt=AtseDBConnection.atseConn.createStatement();
qld_cal_seq = AtseDBConnection.atseConn.prepareStatement("DELETE FROM qld_cal_seq WHERE neg_fr_sl_obj_id = ? ");
neg_fr_sl = AtseDBConnection.atseConn.prepareStatement("DELETE FROM neg_fr_sl WHERE non_cmb_ru_obj_id = ? ");
cat_xpr = AtseDBConnection.atseConn.prepareStatement("DELETE FROM cat_xpr WHERE ncr_alt_obj_id = ? ");
non_cmb_ru = AtseDBConnection.atseConn.prepareStatement( "DELETE FROM non_cmb_ru WHERE obj_id = ? ");
String purgeMonths = properties.getProperty("atse.db.purgeMonths");
if (purgeMonths == null || purgeMonths.equals(""))
{
logger.error("atse.db.purgeDate property is not specified,"+ " exiting");
System.exit(Constants.INITIALIZATION_FAILURE);
}
// final String SELECT_non_cmb_ru = "SELECT obj_id FROM non_cmb_ru where exp_dt < today - " + purgeMonths + " UNITS MONTH ";
final String SELECT_non_cmb_ru = "SELECT obj_id from non_cmb_ru where ( exp_dt < today - 15 units month ) and ( exp_dt > today - 17 units month )" ;
rs=stmt.executeQuery( SELECT_non_cmb_ru );
long rows = 0 ;
while (rs.next())
{
int ctr=0;
String objid=rs.getString("obj_id");
qld_cal_seq.setString(1, objid);
neg_fr_sl.setString(1, objid);
cat_xpr.setString(1, objid);
non_cmb_ru.setString(1, objid);
ctr= qld_cal_seq.executeUpdate();
qld+=ctr ;
ctr=neg_fr_sl.executeUpdate();
neg+=ctr ;
ctr=cat_xpr.executeUpdate();
cat+=ctr ;
ctr=non_cmb_ru.executeUpdate();
non+=ctr ;
rows++;
System.out.println("rows % commitlevel: - " +rows % commitlevel);
if ( rows % commitlevel == 0)
{
AtseDBConnection.atseConn.commit();
rows=1;
}
}
AtseDBConnection.atseConn.commit();
System.out.println("Total Rows deleted from QLD_CAL_SEQ table:" + qld);
System.out.println("Total Rows deleted from NEG_FR_SL Table:" + neg);
System.out.println("Total Rows deleted from CAT_XPR Table:" + cat);
System.out.println("Total Rows deleted from NON_CMB_RU Table:" + non);
//System.out.println("After deleteing the records of objid:"+ objid);
//AtseDBConnection.atseConn.commit();
}
catch (SQLException e)
{
System.out.println ( e.getMessage ());
e.printStackTrace ();
}
finally
{
// Close SQL Objects
try
{
if (rs != null)
{
rs.close();
}
if (qld_cal_seq != null)
{
qld_cal_seq.close();
}
if (neg_fr_sl!=null)
{
neg_fr_sl.close();
}
if (cat_xpr != null)
{
cat_xpr.close();
}
if (non_cmb_ru != null)
{
non_cmb_ru.close();
}
if (stmt != null)
{
stmt.close();
}
AtseDBConnection.atseConn.close();
}
catch(SQLException e)
{
System.out.println (e.getMessage ());
e.printStackTrace ();
}
}
}
this code performs very slowly as the tables which this is refering to has huge amount of data,
Can some one please advice me how can i enhance performance of this code.
Thanks in Adv....
Vivek
