Is there a better way to select and (at the same time) update rows?
An application selects data and then flags each row as processed. It uses a field “Updateable” to indicate if a records has already been processed. Here are relevant parts of thecode:
PreparedStatement stmtFromEACDR = m_DBConn.prepareStatement(
“select * from EACDR where Updateable = ‘Y'”,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet rsEACDRs = stmtFromEACDR.executeQuery();
while( rsEACDRs.next() )
{
// … process record ….
// …. flag the row as processed
rsEACDRs.updateString(“Updateable”,”N”);
rsEACDRs.updateRow();
// …. commit every 5000 records
}
The application needs to process a few million records everyday. According to a performance analyzer, setting “Updateable” field to “N” consumes about 30% of the total time. Can anybody recommend a more efficient way to read and flag records as processed?
Any help would be greatly appreciated.