Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Kindly consider below scenario,

Base Functionality

Reading CSV file and insert in database after replacing values with web macro.

Reading values from csv @ first header information NO,NAME next to that read one by one values from csv and put into web macro context like below context.put("1","RAJARAJAN") next web macro replace $(NO) ==>1 and $(NAME)==>RAJARAJAN values taken from csv once web macro replacement done next add in statement batch once it reached 1000 execute the batch.

Code is running as per functionality but it takes 4 minutes to parse 50,000 records need performance improvement or need to change logic ....kindly let me know if any doubts. Any change to drastic performance...

Note: I use web macro because 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 {  
         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<scan.length;i++){  
                 context.put(header[i],scan[i]);  
             }  
       if(context.size()>0){  
             String m=replacingWebMacroStatement(msg,wm,context);  
             if(counter>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;  
}  
share|improve this question
    
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
4  
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

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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