Faster way of handling many threads updating/adding data to SQL Database

BackGround: i am executing around 80 threads, each thread will continuously add data to a database(around 30k times for each thread)(i am using sql server express). but the slow part of the program being the TableAdapter.Update part.

Why i am asking this question : To know of any faster methods to update the database.

What i am thinking of : Is that to assign a new compact database to each thread and after every 10k entries sync with the actual DB.(But i don't know how to do any part of it)

Broken to bare-bones code :

class Do : Form1
{
    public int i;
    public void FillTable(string[] ws)
    {
        DataRow row = database1DataSet1.Result.NewResultRow();
        row["one"] = ws[0];

        try
        {
            database1DataSet1.Result.Rows.Add(row);
        }
        catch {}
        lock (this)
        {
            try { resultTableAdapter.Update(database1DataSet1.Result); }
            catch { }
        }
    }

    public void start()
    {
        for (;;) //Run about 30k to 40k times
        {
          string[] ws = SOMEFUNC();
          FillTable(ws);
        }   
    } 
}

Calling Code开发者_JS百科:

Do[] case1 = new Do[80];

Thread[] t = new Thread[80];
for (int ij = 0; ij < 80; ij++)
{
    case1[ij] = new Do();
    case1[ij].i = ij;
    t[ij] = new Thread(new ThreadStart(case1[ij].start));
    t[ij].Start();
}

Any ideas?

EDIT : one of the solution: http://www.dotnetcurry.com/ShowArticle.aspx?ID=323

You can do multiple things.

You could do bulk loading either via SqlBulkCopy or (since your on SqlServer 2008) you can pass a table to a stored procedure and have the stored procedure use the MERGE INTO sql command to quicking insert or update the table.

We can get around 200k inserts in 10 seconds using this method.

You could use Dapper instead of TableAdapter. It would get rid of much of the overhead.

In particular there is an enumerable helper you can use:

// begin tran
var items = new[] {{a=1,b=2},{a=2,b=3}};
cnn.Execute("insert table(a,b) values(@a,@b)", items); 
// commit tran

Dapper will be fast however SqlBulkCopy will be much faster as it is designed just for this particular problem.

Honestly, I would ace the 80 threads and start with 1 or 2 and give them more work and do some performance and load testing to see how much they can do, and how fast. That will give you a real idea of how much they can do in x time, to determine if you really need more threads. If you do need more threads, do it incrementally. 2, 3, 4.. 80 threads? That seems insane to me because I don't have a single process (other then core system processes) that even reach close to 80 threads, including SQL Server Enterprise R2 and other things.. If your reason for using 80 threads is "no big reason", then you should probably rethink that. Don't forget that you still have performance limitations due to bandwidth, latency, and other network related things. When talking performance and things that can cause it to slow down, think "what's left?".

Incremental performance testing is my advice on this. Maybe even do research on the objects you are using as they may be costly objects, and maybe look into some alternative methods to pushing your data on through.