• Post Reply Bookmark Topic Watch Topic
  • New Topic

Fill database using multiple threads  RSS feed

 
Amol Pingate
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3648
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 3648
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.



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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!