Concurrency Batch filling the database

Question

I have an embedded SQL DataBase that contains 2 million+ rows with String and Integer fields.
The dataBase is filled by addBatch and executeBatch operations where one batch = 100.000 requests.

The function which create one Batch:

limit = 100000
public void insertData(data) {
   if (insertCounter >= limit) {
     flushToDb();
   }
   prepareInsert.setString(1, data.getString());
   prepareInsert.setString(2, data.getString());
   prepareInsert.setString(3, data.getString());
   prepareInsert.setString(4, data.getString());
   prepareInsert.setInteger(5, data.getInteger());
   prepareInsert.setString(6, data.getString());
   prepareInsertRef.setInteger(7, data.getInteger());
   prepareInsertRef.addBatch();
   insertCounter++;
}

When I use only one thread the database is filled in 13 seconds. However, when I try to add the concurrency my performance does not increase. In my case I create

executorService = Executors.newFixedThreadPool(THREAD_NUMBER);

It executes the InsertDate tasks from the BlockingQueue concurrently, but my program's running time increases to 18 seconds. In the project I use the HSQL database because it supports concurrency write and read operations.

I'd like to hear your ideas on how to improve my multi-threads solution for database filling.

Concurrency Batch filling the database

0 Answers