How to handle Multi-Thread INSERT into Single Table
Posted on 2004-09-15
This is our scenario which I ask for your expert guidance on.
We have a multi-threaded application (approx 40 threads) which are performing searches. Upon finding a successful result, it INSERTS the record into the table.
The application is a Java Application and is working with one DB Connection. Therefore 'one big bottleneck' - especially as the application is trying to insert 2700 records per minute into the table. In addition, the table contains approx 2 million records. (MS SQL)
We are using 1 DB connection as it is only 1 table and we have had issues with adopting multiple connections.
1) Is it best to have multiple DB connections insert into one table?
2) Is there any way of inserting multiple records in 'batch'?
Overall this a bottleneck and without this bottleneck we can acheive much higher performance. I personally find the INSERT statement limiting and hoped to have an batch alternative.
Any help would be much appreciated!!!