Thread: Writing to database (concurrency)

Hello guys,

recently I discovered power of Java, I'm coming from Perl, Python world so I'm at very beginning.
I have firm understanding of OOP and I'm ready for new challenges.

I have question regarding writing to database performance and theory.

Basically as my personal project I'm trying to write some small program or addon which will enable me to write concurrently to database.
Let's say that I have some software and that has FIFO output to database in single stream, but since the output interface is not fast enough congestion can occur if the input to the system is high.
I would like to extend that software with addon which will take that one output stream and create 2,3,4 concurrent input streams towards database. API from that software is provided so I can redirect output to third-party extensions and apps.

Since I'm new I don't even know where to start, connection pooling, connection concurrency,... Can someone explain me what is even needed to write such kind of program and where to gain materials to learn something about this kind of things?
I know that google is my best friend, but it has tons of info and it's easy to lost your head. I know there will be a lot of theory on that before I do something meaningful.

Thank you very much

Originally Posted by andbin

Please, let me understand first: you have 1...N threads that insert informations into the FIFO and only 1 thread that extracts one information and inserts into the DB? And you are saying that the throughput of the extractor is not sufficient for the incoming informations? Is it correct?

Before to resort to multiple extractor threads, you should evaluate (and explain here) other things: are you using JDBC directly? Or a library on top of JDBC (e.g. Hibernate)? Are you using the same connection for all inserts or are you getting a new connection for each insert? (this would be a big bottleneck). Then, how much complex is the insert on DB? How the application is connected to the database? (local machine? on a local network?)

These are just some questions ...

Thank you for reply.

All stated questions are valid. The application in front communicates towards various systems via Radius or Diameter. Depending on config there can be multiple interfaces or just one, by each interface one thread. In any case, since this are highly transactional systems which can go to 400 TPS per interface and DB output is limited only to 1 thread or better to say 1 FIFO stream for writing to it.

I haven't think about communication since this will be just challenge for me. But let's say that DB to which I'm writing is on other server and the inserts are simple. 30 - 50 values per insert, no updates or complicated stored procedures behind it. And I'm planning to use no third party extensions.

I know this is big peace of cake for me right now, but that is I would like to have systematic path of progress and also to have some pointers in which direction to go.

Thank you