SQL Server Database Update using Multithreading

Question

I have a requirement to update 20 tables in a SQL Server database in a C# application. For better performance, I am planning to use multiple threads for updating tables. Could anybody refer any example link which gives idea for this kind of operation?

Also, as per my understanding, if I use multi threading, then I have to use different connection object for each thread. In that case, how I can put multiple threads in a single transaction, which are basically using different connection objects?

Did you try using one stored procedure call which may then update 20 tables. so you would not have to open (even multithreaded) 20 connections? — Pilgerstorfer Franz
– Pilgerstorfer Franz, Commented Oct 18, 2013 at 18:44
Are you sure you need multiple threads? You will get better performance only if constructing sql statements takes significant time. If most of the time is spent executing statements then using multiple threads might even make whole process slower. — empi
– empi, Commented Oct 18, 2013 at 18:46
Why would you need to multithread this? It just feels like a bad idea. 20 tables is nothing. — Dave Zych
– Dave Zych, Commented Oct 18, 2013 at 18:47
Write some code to just do the 20 updates first! Don't just jump on multi-threading to "speed" it up before you even know how long it's taking and where the time is going (code vs DB)..... — Vivek
– Vivek, Commented Oct 18, 2013 at 18:49
So if Update "333" (some random update) to Table15 doesn't work, does that not matter to the entire operation? — granadaCoder
– granadaCoder, Commented Oct 18, 2013 at 19:05

safeer ahmad · Accepted Answer · 2013-10-18 19:53:01Z

1

Use TPL (task parallel library), here is an example http://safeery2k.wordpress.com/2013/09/17/ado-net-using-tpl/

answered Oct 18, 2013 at 19:53

safeer ahmad

259 bronze badges

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

Comments

Andrew Mack · Accepted Answer · 2013-10-18 19:47:34Z

0

20 tables to update at once is pretty crazy.

If 20 stored procedures is the ONLY way to do what needs to get done then create one more stored procedure that will call the 20 stored procedures for you and simply use this stored procedure.

This way if an error/exception occurs you will be able to rollback the mods AND it is now contained in one location - nice and easy (all things considered).

I'm very glad I don't have to deal with the situation you're in with this one! Good luck with this!

answered Oct 18, 2013 at 19:47

Andrew Mack

3123 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL Server Database Update using Multithreading

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related