c# am I handling multiple threads properly

Question

I have an winform application that creates 5 threads to connect to and retrieve information from a database on a very slow connection (90 seconds for some queries).Each thread has it's own instance of a class for performing sql queries. When the queried data has been retrieved the main thread is notified by an event fired from the class running the query. After receiving the event various components of the main thread are updated such as display items or just a datatable holding data for later use. The queries are repeated at various intervals depending on the type of information that they are querying.

Everything works great...but I am not happy. I feel that it should be done a different way, but I am not sure which way.

Below is how I currently set up each thread:

string ConnectionString = @"Data Source=mySrv;Initial Catalog=myTbl;Connect Timeout=30;UID=sa;pwd=mypwd";


//thread #1    
 SQL_Requests ReasonRequests;
 Thread Reason_Thread;
 ReasonRequests = new SQL_Requests();
 ReasonRequests.ProcessFinished += new SQL_Requests.ProcessFinished(ReasonRequests_Completed);
 Reason_Thread = new Thread(ReasonRequests.ExecuteQuery);
 ReasonRequests.DBQueryString = "select * from ReasonTable where staralingment = goodalignment"
 ReasonRequests.DBConnection = ConnectionString;

//thread #2
SQL_Requests EventRequests; 
Thread Event_Thread;
EventRequests = new SQL_Requests();
EventRequests.ProcessFinished += new SQL_Requests.ProcessFinished(EventRequests_Completed);
Event_Thread= new Thread(EventRequests.ExecuteQuery);

EventRequests.DBQueryString = "select * from EventTable where somefield = somevalue"
EventRequests.DBConnection = ConnectionString;

each Thread.start are at different intervals.

any recommendations?

Answer 1

Without knowing specifics, this leaves a bad taste in my mouth, too. I'm sure that it works. Whether it's dangerous depends on whether your main class is thread-safe or not. It sounds like you need to do some serious testing re: what happens when two requests are completed at the same time that both just did something with the same data.

Honestly, if I were to look for a "different way" to do this, I'd probably skip the multiple-thread access to the database, have a single thread doing all the database work, with worker threads doing any extra work that needs to happen (if any), and then reporting back to the main thread for DB access.

Answer 2

The typical reason you'd want to do this is if each of the 5 database queries was lengthy and there was some performance gain to be had by running them in parallel. If you're not going to get a performance gain from doing this, I would simply use one thread to do the database work and report its progress to the UI.

If there is a performance benefit to be had from parallel processing, I would use the built in thread pool (System.Threading.ThreadPool.QueueUserWorkItem). Unless you need some more control over the threads, the thread pool is perfect for "fire and forget" type operations. You queue up an operation, and it invokes a delegate when it's done.

Answer 3

If your threads fetch data from the same server over a very slow connection (meaning that limited bandwidth is the main factor) you will not gain anything by using multiple threads.

OTOH it could actually be better to use a single thread for all data fetch operations:

• You will get a portion of the data after some time, so you can update the UI with that. Fetching in parallel would probably split the bandwidth, and you would get a long time without any data, and in the end the results would arrive shortly after another. Your UI would look less responsive that way.

• If the selects cause a lot of I/O on the server, not having them execute in parallel could actually result in better throughput. Consider that other operations will be executed on the server too.

IMHO you should keep the fetches in a thread for best responsiveness of the UI, but use only one.

Edit: You state in the comment that fetches may take different amounts of time. If you can estimate which queries will be the fastest to complete the transfer, execute them first. Still assuming of course that the data transfer takes most of the time, not the query execution on the server.

If you can't estimate how long queries will take, or if bandwidth isn't the only limitation, using multiple threads may of course work best for you.

Answer 4

Instead of spinning up your own threads you should take a look as the asynchronous methods for executing queries i.e. http://msdn.microsoft.com/en-ca/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx

You mentioned that your connection is slow, is it a low bandwidth connection or a high latency connection? If the data is being returned slowly because of insufficient bandwidth firing off multiple queries will just make things slower. If it is just a latency issue doing multiple queries at once may improve responsiveness.

If you are performing a group of related queries you may also want to consider grouping them into a single command, or grouping them on the server by using a stored procedure. You can get additional result sets by using the NextResult method on the SqlDataReader.

Answer 5

Instead of using plain threading to develop concurrent software, please also consider task parallelism.

Please read the following articles: http://blog.rednael.com/2009/02/05/ParallelProgrammingUsingTheParallelFramework.aspx http://blog.rednael.com/2009/03/16/ParallelFrameworkDownloadAndSupport.aspx

These are articles about basic parallel programming and also contain references to other articles about the background of parallelism. Included are examples in C# .Net. Also, it describes a lightweight parallel framework to work with tasks. Opposed to some other frameworks, this one is very light and very easy to use. After reading these articles, you should be able to write code using parallelism.

Regards, Martijn