Link to home
Start Free TrialLog in
Avatar of Angus
AngusFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to handle Multi-Thread INSERT into Single Table

Folks,
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.

Our questions:
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!!!

Thanks
Angus
ASKER CERTIFIED SOLUTION
Avatar of BoyPupa
BoyPupa

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial