CODEWITHSUNDEEP
javasqloracle-databasejdbc
arsenal
arsenal

Reputation: 24194

Insert many rows to Oracle Database very fast

I need to insert many sql rows into oracle database very fast. IndexData is the class which contains save method to insert into oracle database.

while ((line = in.readLine()) != null) {
    if(line.contains("numDocs")) {
        numDocs = in.readLine().trim();
//For Inserting 
IndexData id = new IndexData(timeStamp, 1, 2, numcDocs);
id.save();
    }  else if(line.contains("indexSize")) {
        indexSize = in.readLine().trim();
//For Inserting 
IndexData id = new IndexData(timeStamp, 1, 3, indexSize);
id.save();
        } else if(line.contains("indexReplicatedAt")) {
   replicationTime = in.readLine().trim();           
//For Inserting         
IndexData id = new IndexData(timeStamp, 1, 4, replicationTime );
id.save();
    } 
    }

    BufferedReader inUrl   = new BufferedReader (new InputStreamReader (isUrl));
    String lineUrl;
    Pattern regex = Pattern.compile("<str name=\"status\">(.*?)</str>");

    while ((lineUrl = inUrl.readLine()) != null) {
    if(lineUrl.contains("str name=\"status\"")) {
        Matcher regexMatcher = regex.matcher(lineUrl);
        if (regexMatcher.find()) {
        status = regexMatcher.group(1);
//For Inserting
IndexData id = new IndexData(timeStamp, 1, 5, status);
id.save();
        }                   


}  
}

And this is my IndexData class-

public IndexData(String timestamp, String serveId, String informationId, String value ) {
this.timestamp=timestamp;
this.serverId=serverId;
this.informationId=informationId;
this.value=value;
}
//For Inserting
public void save() throws SQLException {

ps = DataSource.conn.prepareStatement (
"INSERT INTO table(timestamp, serverId, informationId, value) VALUES(?,?,?,?)");

ps.setString (1, timestamp);
ps.setString (2, serverId);
ps.setString (3, informationId);
ps.setString (4, value);

ps.executeUpdate();
ps.close ();
}

Is this the best way to insert into oracle database having several multiple sql statement for insertion. As I am opening IndexData class again and again for inserting only one row into DB for each information. Is there any other way that is faster than this. Any suggestions will be appreciated..

Upvotes: 0

Views: 5989

Answers (4)

Thebestshoot
Thebestshoot

Reputation: 208

One thing you can do is to write the queries to a file and load the file into DB in a separate thread. This is because all your quires are just insert queries. This has a glitch although that if the loading of file fails then you don't know where its wrong. This way you can reduce the memory foot print and work faster is what I guess.

Upvotes: 0

steve
steve

Reputation: 6020

This will never scale very well, but it depends how many records need to be loaded. Generally a better option is to use direct path load with sql loader and potentially load in parallel.

Upvotes: 2

Adrian Shum
Adrian Shum

Reputation: 40076

I feel bad smell in your design, like your entity object is responsible for creating and closing the prepare statement, database connection is "global" etc.

There are some common way that you can look into:

  1. Perform prepare statement only once and use it for multiple update
  2. Use Batch Insert/Update

However, both way needs you to revisit your design (but I think it is worth doing so).

(And, if you are posting your code, please make sure you properly indent it. It is simply a mess now)

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1504022

Have a look at PreparedStatement.addBatch and PreparedStatement.executeBatch - you can create a single prepared statement, add several sets of parameters for of execution, and then execute the batch. You probably want to choose some number of inserts per batch though, limiting the amount of "pending" work building up in memory.

Upvotes: 6

Related Questions