Fill database using multiple threads

[Amol Pingate]

Hello Friends,

i am confused with this problem. How should i manage the reading record from text file and fill database to decrease the execution time.

My text file is containing records , that i have to split first and then i am inserting the separated data into the database.


please help.

[K. Tsang]

If I understand you correctly, you got a text file and a physical DBMS like MySQL or Oracle then you can use 1 thread to read data off the text file and another thread to insert data to the DB.

This is like producer/consumer problem. The text file thread is the producer and DB is the consumer. The DB thread listens for any data from the producer, do nothing until there is data.

Another way to look at it is more like doing batch inserts for the DB. Read whole bunch of lines of data then do single insert to the DB.

Either way seems fine to me. The first method is probably better performance wise. The second method may cause SQL problems.

[Amol Pingate]

Currently my program is working with following steps

1)Read Record
2)Split Record
3)Check Record is present in DB or not (To avoid duplication)
4)if not present then Fill DB
5)Repeat above steps till end of file.


This process is taking near about 24 sec to complete without executing step3 for near about 540 records in text file

i wanna reduce that time.

and if i execute it with step 3 then it will take more time(i think).

please suggest me

[K. Tsang]

Amol Pingate wrote:Currently my program is working with following steps

1)Read Record
2)Split Record
3)Check Record is present in DB or not (To avoid duplication)
4)if not present then Fill DB
5)Repeat above steps till end of file.

Then you can have 1 thread to read (step 1) then from that start a new thread for steps 2,3,4.

// main class
RandomAccessFile raf = new RandomAccessFile("filename.txt", "r");
String line = null;
while ((line = raf.readLine()) != null) { // read line from file
  new Record(line).start(); // a thread will start for every record read 
}

// Record.java
class Record implements Runnable {
  private String data;

  public Record(String str) {
     data = str;
  }

  public void run() {
     // split data, check db, write data
  }

}

If you want you can split up steps 3 and 4 to their own threads as well. Also for the data checking, if the file record number corresponds to the db table's primary key then your sql (step 3) can be faster.