Merging CSV data into database Base functionality:


Reading a CSV file and inserting in database after replacing values with web macro.
Reading values from CSV @ first header information NO,NAME next to that, then read one by one values from CSV and put into web macro context like below:

context.put("1","RAJARAJAN")

Web macro will replace $(NO) ==&gt;1 and $(NAME)==&gt;RAJARAJAN values taken from CSV once the web macro replacement is done.
Add in statement batch.  Once it reaches 1000, execute the batch.


The code is running as per functionality but it takes 4 minutes to parse 50,000 records.  I need a performance improvement or a change in logic.

Note: I use web macro to replace $(NO) in merge query.

Bala.csv  

NO?NAME  ==================&gt;Header Information
1?RAJARAJAN  
2?ARUN  
3?ARUNKUMAR  

Connection con=null;  
Statement stmt=null;  
Connection con1=null;  
int counter=0;  
 try{  
      WebMacro wm = new WM();  
      Context context = wm.getContext();  
      String strFilePath = "/home/vbalamurugan/3A/Bala.csv";  
      String msg="merge into temp2  A using  
      (select '$(NO)' NO,'$(NAME)' NAME from dual)B on(A.NO=B.NO)  
               when not matched then  insert (NO,NAME)  
                  values(B.NO,B.NAME) when matched then  
                  update set A.NAME='Attai' where A.NO='$(NO)'";   
      String[]rowsAsTokens;  
      con=getOracleConnection("localhost","raymedi_hq","raymedi_hq","XE");  
      con.setAutoCommit(false);  
      stmt=con.createStatement();  
      File file = new File(strFilePath);  
      Scanner scanner = new Scanner(file);  
     try {  
         String headerField;  
         String header[];  
         headerField=scanner.nextLine();  
         header=headerField.split("\\?");  
         long start=System.currentTimeMillis();  
         while(scanner.hasNext()) {        
             String scan[]=scanner.nextLine().split("\\?");  
             for(int i=0;i&lt;scan.length;i++){  
                 context.put(header[i],scan[i]);  
             }  
       if(context.size()&gt;0){  
             String m=replacingWebMacroStatement(msg,wm,context);  
             if(counter&gt;1000){  
                 stmt.executeBatch();  
                 stmt.clearBatch();  
                 counter=0;  
             }else{  
                 stmt.addBatch(m);  
                 counter++;  
             }  

           }  
       }  
   long b=System.currentTimeMillis()-start;  
   System.out.println("=======Total Time Taken"+b);  
     }catch(Exception e){  
        e.printStackTrace();  
    }  
  finally {  
     scanner.close();  
   }        
          stmt.executeBatch();  
          stmt.clearBatch();  
          stmt.close();  
    }catch(Exception e){  
      e.printStackTrace();  
      con.rollback();  

  }finally{  
      con.commit();  

  }  

 // Method For replace webmacro with $  
  public static String 
  replacingWebMacroStatement(String Query, WebMacro wm,
      Context context)   throws Exception {  

Template template = new StringTemplate(wm.getBroker(), Query);  
template.parse();  
String macro_replaced = template.evaluateAsString(context);  
return macro_replaced;  
}  
 // for getting oracle connection  
 public static Connection 
  getOracleConnection(String IPaddress,
  String username,String  password,String Tns)throws SQLException{

  Connection connection = null;  
  try{  
  String baseconnectionurl ="jdbc:oracle:thin:@"+IPaddress+":1521:"+Tns;  
  String driver = "oracle.jdbc.driver.OracleDriver";  
  String user = username;  
  String pass = password;  
  Class.forName(driver);  
  connection=DriverManager.getConnection(baseconnectionurl,user,pass);  
  }catch(Exception e){  
   e.printStackTrace();  
  }  
  return connection;  
}
It appears that you are reading records from a file, processing them in some way and then using the processed data to insert records into a table. Some of the processing involves getting data out of the database.

It looks like you are creating one insert statement for each row from the source file.

I think you would be more likely to get better performance if you broke the processing up into two parts. First, read the file, process the data and create the final version that is going to go into the database. Then, in a separate operation, load the data into the database.

In the first part of the process, you may be able to find ways to improve the performance by caching query results.

You may be able to write the results of the processing out to a temporary file that can be easily imported using an Oracle tool. This question may be helpful