Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to handle Multi-Thread INSERT into Single Table

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
SQL USE DATABASE VARIABLE 5 28
SQL Quer 4 22
Index and Stats Management-Specific tables 8 22
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

837 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