Solved

How to handle Multi-Thread INSERT into Single Table

Posted on 2004-09-15
1
1,079 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
Need help how to find where my error is in UFD 6 28
sql server tables from access 18 17
insert wont work in SQL 14 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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