# Merging CSV data into database

Base functionality:

1. Reading a CSV file and inserting in database after replacing values with web macro.
2. 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")

3. Web macro will replace $(NO) ==>1 and $(NAME)==>RAJARAJAN values taken from CSV once the web macro replacement is done.
4. 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 ==================>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 {
long start=System.currentTimeMillis();
while(scanner.hasNext()) {
String scan[]=scanner.nextLine().split("\\?");
for(int i=0;i<scan.length;i++){
}
if(context.size()>0){
String m=replacingWebMacroStatement(msg,wm,context);
if(counter>1000){
stmt.executeBatch();
stmt.clearBatch();
counter=0;
}else{
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

Connection connection = null;
try{
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
connection=DriverManager.getConnection(baseconnectionurl,user,pass);
}catch(Exception e){
e.printStackTrace();
}
return connection;
}

-
The way this question is worded, it is hard to follow exactly what you are asking. Also, the indentation of the code is not correct, and this is making it hard to understand. –  Donald.McLean Aug 22 '12 at 13:32
The biggest speed gains will come from using a "Bulk Insert" feature directly or indirectly. Here is a relevant SO entry: stackoverflow.com/questions/2716818/… –  Leonid Aug 22 '12 at 13:57