Solved

How to handle Multi-Thread INSERT into Single Table

Posted on 2004-09-15
1
1,058 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:amacfarl
1 Comment
 
LVL 2

Accepted Solution

by:
BoyPupa earned 50 total points
ID: 12069812
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 ; )
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now