Home How do I increase performance on data load for large text file
Reply: 0

How do I increase performance on data load for large text file

user1689
1#
user1689 Published in June 19, 2018, 7:59 pm

This question already has an answer here:

  • Efficient way to do batch INSERTS with JDBC 8 answers

I am trying to insert a large text file into oracle database, my current program works but it is loading very slow. The text file is around 400 MB

What I did was like below...

...
ArrayList<String> nta = new ArrayList<String>();
while ((line = br.readLine()) != null) {
//add data position for each line read
data.add(line.substring(0, 6)+
"="+line.substring(6, 24)+
"="+line.substring(24, 30)+
"="+line.substring(30, 48)); 

}
db.insertRecord(data);
...

public void insertRecord(ArrayList<String> data) {
String sql = "Insert into Account (NAME, ID, RCBS, CA_NUM, GUID, PARN_GUID)"+
             " values "
             "(?,?,?,?,?,?)";
...
ps = con.prepareStatements(sql);
for(int i=0; i<data.size(); i++) {
    String[] fields = data.get(i).split("=");
            ps.setString(1, fields[0]);
            ps.setString(2, fields[1]); 
            ps.setString(3, fields[2]);
            ps.setString(4, fields[3]); 
            ps.setString(5, fields[0].trim()+"."+fields[1].trim()+"."+fields[2].trim()+"."+fields[3].trim()); //Index
            ps.setString(6, fields[0].trim()+"."+fields[1].trim()); //PARN Index
            ps.execute();
} //end loop
con.commit();
...

Are there any performance can be done to increase the speed of the data load?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.52314 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO