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

Question

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:

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

Is there any reason you're using 80 threads? For the most part, you'll probably see a performance decrease as opposed to using fewer threads due to excessive context-switching — Rob
– Rob ♦, Commented Jul 3, 2011 at 6:12
No big reason, but how can you determine the optimum number of threads that may be used? — TarunG
– TarunG, Commented Jul 3, 2011 at 6:13

Kyro · Accepted Answer · 2011-07-03 07:30:03Z

2

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.

answered Jul 3, 2011 at 7:30

Kyro

7681 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TarunG Over a year ago

So are you also suggesting that i should assign a different compact DB to each thread, and at the end merge it with teh main DB?

Kyro Over a year ago

No, you should really just go to one database and one or two threads (80 threads is pointless unless your dealing with data from 80 different sources).

Sam Saffron · Accepted Answer · 2011-07-04 23:42:25Z

1

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.

edited Jul 4, 2011 at 23:42

Sam Saffron

132k81 gold badges334 silver badges511 bronze badges

answered Jul 3, 2011 at 6:00

cesara

8421 gold badge7 silver badges19 bronze badges

Comments

David Anderson · Accepted Answer · 2011-07-03 07:20:14Z

0

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.

answered Jul 3, 2011 at 7:20

David Anderson

13.7k5 gold badges53 silver badges80 bronze badges

4 Comments

TarunG Over a year ago

Thanks for your advice. but let me simplify my "no big reason", as evident from the code that each thread runs a separate loop for each value assigned to the Do class member i, Now if i do it with 2 or 3 threads then the work will be done sequentially. Agreed on the part of performance limitations due to network related things, i want to mainly speed up the Update Table part(The DB is on local server).

David Anderson Over a year ago

Are you possibly opening a new connection every time to do an update?

TarunG Over a year ago

No, i am calling the TableAdapter.Update everytime to add a single row each time.

David Anderson Over a year ago

You're going to have to study the ins-and-outs of the SqlDataAdapter (that's what the data set and table adapters use under-the-hood). I would try to do bulk commits, and not a single row at a time because a lot happens when AcceptChanges is called. If that can't help by doing bulk commits with the existing code you have, then you will want to look into some other options. When it comes to performance you just have to continously measure, measure, measure, and measure, measure. I would try multiple options and run performance tests on each.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related