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

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

logger
1#
logger Published in 2017-10-27 14:13:36Z

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?

CrazySabbath
2#
CrazySabbath Reply to 2017-10-27 14:41:45Z

As suggested, you should use batch inserts when dealing with that many records. Note that this piece of code will execute inserts once every 1000 (not all at once) to avoid possible memory problems, batchInterval.

int batchInterval = 1000;

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.addBatch();

        if (i % batchInterval == 0) ps.executeBatch();
}

ps.executeBatch();
ps.close();
con.close();

You can find more here: batch inserts tutorial

Needle file
3#
Needle file Reply to 2017-10-27 14:16:24Z

send it to server as CLOB (many-many row in one portion) and parse on server side..

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO