Search code examples
javaoracle-databasejdbcsql-tuning

Making JDBC insert query faster

I am fetching data from a master table(oracle) and keep adding it to Entity List until the whole data is fetched. The master table has 45 columns. Then I traverse the list and insert each row into my local table(oracle).

The schema for local table is not exactly the same as original table. There are 3 extra columns in local table, the data in which is processed from other columns only. So, after processing the complete row is being added to the local table.

The insert query is taking a lot of time, even though i have not created indexes on table yet. How to make the insert faster?

Solution

  • Use a batch of inserts. Something like this:

    Connection connection = new getConnection();
Statement statement = connection.createStatement();

for (String query : queries) {
    statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();

    See the full example here:http://viralpatel.net/blogs/batch-insert-in-java-jdbc/