3
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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";
    
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.