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
LVL 2
amacfarlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BoyPupaCommented:
sql is naturally meant to operate in "batch" mode, in is only applications languages (like java) that prefer to work one row at a time.  If you're sure that the insert process into this table is your bottleneck (and I'm assuming you've tested and are sure of this) there are some of things you can do.  
 Is this table heavily indexed?  indexes speed up reads, but slow down writes.  ( I hope this isn't isn't one of the tables you're searching all the time)
 Does it have a natural clustered index (ie is the clustered key on this table composed of the data itself eg lastname,firstname,zip)  if so then such then the data you're inserting may require that other data be physically relocated to make room since data in sql server tables is stored on disk in the order of clustered index.  This can be good or bad depending on your hardware config.  If the database is on a disk array then spreading your inserts around randomly is probably a good thing. if it's sitting on a single disk, is the rest of that database (the part being searched) also on the same disk?  if so it may make sense to create multiple disk filesgroups, put this file into it's own filegroup and move it to it's own disk.  Then maks sure that the table stores its inserts serially (latest insert writes to end of the table)  that way the disk head is just sitting there waiting to write the next record.

Finally, to literally answer the question you asked: there is no reason why each of your clieit threads couldn't write to it's own temp table or otherwise defined buffer storage, which could then be moved into the destination table in "batch" mode by executing  
insert into table 1
select stuff.. from #client_1_table

I'm not sure what this application is trying to do, so I can't give real specific advice, but it the data it's writing is composed entirely of the data it's reading (Ie no user input is being stored for each line) then why not write a stored procedure to go find large groups or the desired data all at once and copy it to the table without ever making the round trip to and from the java program?  What identifies these records that you want?  Unless they are being arbitrarily chosen by an end user, the criteria should be easily translatible into sql to allow for set based finds as well as writes.  If, on the other hand, you are actually hosting a web site or some other application that is generating 2700 user interactions per minute that need to be recorded (orders etc) then you should probably contract with a dba to spend some serious time on this ; )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.