In a project I am working on, I am given a list that contains a little under 1 million lines. The data maps all possible origins (000-999) to all possible destinations (000-999).
For each combination, I need to be able to look at the database and determine if there exists a record with the same combination. If no record exists, then it will be added to the database. If the record does exist, then the record will be updated with the new information.
The origin and destination are the primary key of the table, and are also indexes. This is all on an ORACLE database.
Given that I have to do this 1 million times, what is the best possible solution? My current method is taking upwards of an hour to process all records.
For actually inserting and updating the records, I am using a batch query process that doesn't take much time at all.
The part that appears to be taking the most amount of time is querying the database for the count of existing records.
public String batchUpdateModes(List records, String user) throws TransactionException {
String message = "";
ArrayList updateList = new ArrayList();
ArrayList insertList = new ArrayList();
Connection conn = null;
try {
conn = getDao().getConnection();
} catch (SQLException e1) {
e1.printStackTrace();
}
for (int i = 0; i < records.size(); i++) {
BatchFileCommand record = (BatchFileCommand)records.get(i);
String origin = record.getOrigZip().trim();
String dest = record.getDestZip().trim();
String pri = record.getPriMode().trim();
String fcm = record.getFcmMode().trim();
String per = record.getPerMode().trim();
String pkg = record.getPkgMode().trim();
String std = record.getStdMode().trim();
String effDate = record.getEffDate();
String discDate = "";
TransModeObj obj = new TransModeObj(origin, dest, pri, fcm, per, pkg, std, effDate, discDate);
obj.setUserId(user);
try {
Statement stmt = null;
String findExisting = "select count(*) from trans_mode where orig_zip = " + origin + " " +
"and dest_zip = " + dest;
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(findExisting);
int count = 0;
while (rs.next()) {
count = rs.getInt(1);
}
if (count > 0) {
updateList.add(obj);
}
else {
insertList.add(obj);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
message = e.getMessage();
}
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
boolean success = false;
recordCount[0] = updateList.size();
recordCount[1] = insertList.size();
success = insertTransModes(insertList);
System.out.println("Inserts Complete");
success = updateTransModes(updateList);
System.out.println("Updates Complete");
if (success) {
message = "success";
}
else {
message = "The changes or additions submitted could not be completed.";
}
return message;
