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

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 2

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.