Oracle to MSSQL data migration is slow on INSERT

Question

I wrote a method that gathers data from an Oracle server, formats and encrypts the data then inserts it into a MS SQL server. The method moves about 60000 records and takes a bit long and is a little sloppy. Can anyone see places to clean it up and make it faster?

Two of the areas that I see might need improvements are when the result set is being added to the List. And when the List is being inserted 1000 rows at a time into the MS SQL table.

Here is the code:

public static void get_random_selection(Connection ora_conn, Connection sql_conn) throws Exception, SQLException{
 Statement sql_stmt = sql_conn.createStatement();
 Statement ora_stmt = ora_conn.createStatement();
 ResultSet sql_rs = null;
 ResultSet ora_rs = null;
 //Select the max QUARTER from RANDOM_SELECTION in MS SQL
 sql_rs = sql_stmt.executeQuery("SELECT MAX(QUARTER) FROM RANDOM_SELECTION");
 sql_rs.next();
 int max_from_mssql = sql_rs.getInt(1);
 ora_rs = ora_stmt.executeQuery("SELECT MAX(QUARTER) FROM RANDOM_SELECTION");
 ora_rs.next();
 int max_from_oracle = ora_rs.getInt(1);
 //If the max_from_oracle is larger than max_from_mssql than the AL's and RL's in Oracle
 //are fresher and need to be moved to MS SQL
 //if (max_from_oracle > max_from_mssql){
 if(1==1){
 System.out.println("The RANDOM_SELECTION table in Oracle is more up to date than the RANDOM_SELECTION table in MS SQL.");
 System.out.println("Retrieving RANDOM_SELECTION data from Oracle.");
 //select items from RANDOM_SELECTION and DROPPER_CITY_BRK_2 that need to be moved
 ora_rs = ora_stmt.executeQuery("select distinct(random_selection.randnum), " 
 + "random_selection.quarter, " 
 + "random_selection.ozip3, "
 + "random_selection.boxid, " 
 + "random_selection.boxaddr, " 
 + "random_selection.locdesc, " 
 + "random_selection.loccity, " 
 + "random_selection.lastmf, "
 + "random_selection.lastsat, "
 + "random_selection.boxtype, "
 + "random_selection.svcclas, "
 + "random_selection.dropzip5, "
 + "random_selection.dropper_id "
 + "from random_selection "
 + "where random_selection.dropper_id is not null "
 + "and random_selection.quarter = " + max_from_oracle + " "
 + "union "
 + "select distinct(random_selection.randnum), "
 + "random_selection.quarter, " 
 + "random_selection.ozip3, "
 + "random_selection.boxid, " 
 + "random_selection.boxaddr, " 
 + "random_selection.locdesc, " 
 + "random_selection.loccity, " 
 + "random_selection.lastmf, "
 + "random_selection.lastsat, "
 + "random_selection.boxtype, "
 + "random_selection.svcclas, "
 + "random_selection.dropzip5, "
 + "dropper_city_brk_2.dropper_id "
 + "from random_selection, dropper_city_brk_2, dropper "
 + "where random_selection.ozip3 = dropper_city_brk_2.zip3 "
 + "and dropper.dropper_id = dropper_city_brk_2.dropper_id "
 + "and dropper.active = 1 "
 + "and dropper_city_brk_2.dropper_id <> 10002 "
 + "and random_selection.quarter = " + max_from_oracle + " "
 + "and random_selection.dropper_id is null");
 System.out.println("Retrieved RANDOM_SELECTION data from Oracle.");
 List<String[]> random_selection = new ArrayList<String[]>();
 System.out.println("Assigning ResultSet to List.");
 while (ora_rs.next()){
 random_selection.add(new String[]{
 ora_rs.getString("RANDNUM"),
 ora_rs.getString("QUARTER"),
 ora_rs.getString("OZIP3"),
 ora_rs.getString("BOXID"),
 ora_rs.getString("BOXADDR").replace("'"," "),
 ora_rs.getString("LOCDESC") == null ? ora_rs.getString("LOCDESC") : ora_rs.getString("LOCDESC").replace("'",""),
 ora_rs.getString("LOCCITY").replace("'", " "),
 ora_rs.getString("LASTMF"),
 ora_rs.getString("LASTSAT").equals("11:58pm") ? "null": ora_rs.getString("LASTSAT"),
 ora_rs.getString("BOXTYPE"),
 ora_rs.getString("SVCCLAS"),
 ora_rs.getString("DROPZIP5"),
 ora_rs.getString("DROPPER_ID")});
 System.out.println(ora_rs.getRow());
 }
 System.out.println("Finished assigning ResultSet to List.");
 //leading statement for the following loop
 String query = "insert into random_selection "
 + "(RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";
 int jj = 0;
 //loop through random_selection_array creating an INSERT statement to insert 999 entries at a time
 //this is done to speed up the process
 for(int ii = 0;ii<random_selection.size();ii++){
 String[] array_holder = random_selection.get(ii);
 query = query
 + "("
 + "'"+array_holder[0]+"',"
 + "'"+array_holder[1]+"',"
 + "'"+array_holder[2]+"',"
 + "'"+array_holder[3]+"',"
 + "'"+array_holder[4]+"',"
 + "'"+array_holder[5]+"',"
 + "'"+array_holder[6]+"',"
 + "'"+array_holder[7]+"',"
 + "'"+array_holder[8]+"',"
 + "'"+array_holder[9]+"',"
 + "'"+array_holder[10]+"',"
 + "'"+array_holder[11]+"',"
 + "'"+new sun.misc.BASE64Encoder().encode(encrypt(array_holder[12]))+"'),";
 //every 999 iterations enter here
 if (jj > 998){
 //add |%| to the end of the string so that you can remove the final ','
 query = query+"|%|"; 
 query = query.replace(",|%|","");
 System.out.println(query);
 //sql_stmt.executeUpdate(query);
 query = "insert into random_selection (RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";
 jj = 0;
 }
 jj++;
 //the last few entries will be added one at a time to prevent nulls records from being inserted
 if (ii > (random_selection.size() / 999) * 999){
 //add |%| to the end of the string so that you can remove the final ','
 query = query+"|%|";
 query = query.replace(",|%|","");
 System.out.println(query);
 //sql_stmt.executeUpdate(query);
 query = "insert into random_selection (RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";
 }
 }
 }
}

The client wants to refrain from any open connections between the two servers.

• Andrew T Finnell: Or try this microsoft.com/download/en/… and pray it works like they say it does.

Answer 1

First of all, profile the code to find the bottleneck.

Some other idea:

• Use threads. One thread retrieves the data from Oracle and one or more threads upload it to MSSQL.
• Use PreparedStatements for inserting data.
• Disable indexes in MSSQL during the migration.

Some generic notes:

1. It's unnecessary to assign null to variables:

   ResultSet sql_rs = null;
   ...
   sql_rs = sql_stmt.executeQuery("SELECT MAX(QUARTER) FROM RANDOM_SELECTION");
   

   You could simply write this:

   ...
   ResultSet sql_rs = sql_stmt.executeQuery("SELECT MAX(QUARTER) FROM RANDOM_SELECTION");
   

2. Instead of the String[] array (whose indexes are magic numbers) I'd use a data object with named fields.

   public class RandomSelection {
    private String randNum;
    private String quater;
    ...
   }
   

3. Use the Code Conventions for the Java Programming Language.

   • get_random_selection should be getRandomSelection,
   • ora_stmt should be oracleStatement,
   • etc.

4. Split the method at least to two smaller one: one should handle retrieving and another one should create the insert statements.

5. Keep it DRY, repeated codes should be moved to a new method.

   query = query+"|%|";
   query = query.replace(",|%|","");
   System.out.println(query);
   //sql_stmt.executeUpdate(query);
   query = "insert into random_selection (RANDNUM,QUARTER,OZIP3,BOXID,BOXADDR,LOCDESC,LOCCITY,LASTMF,LASTSAT,BOXTYPE,SVCCLAS,DROPZIP5,DROPPER_ID) VALUES";