C # I am handling multiple threads correctly

Question

I have a winform application that creates 5 threads for connecting and retrieving information from a database on a very slow connection (90 seconds for some queries). Each thread has its own instance of the class to execute sql queries. When the requested data has been received, the main thread is notified by an event fired from the class executing the request. After receiving the event, various components of the main stream are updated, such as display elements or just data to store data for later use. Requests are repeated at different intervals depending on the type of information they request.

Everything works fine ... but I'm not happy. I feel that this should be done differently, but I'm not sure which direction.

The following shows how I configure 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 has different intervals.

any recommendations?

Answer 1

Instead of unscrewing your own threads, you should look like asynchronous methods for executing queries, i.e. http://msdn.microsoft.com/en-ca/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx

Did you mention that your connection is slow, is it a low bandwidth connection or a high latency connection? If the data is slowly returned due to insufficient bandwidth that drops several requests, it will simply slow down. If this is just a latency problem that executes multiple requests at once, it can improve responsiveness.

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

Answer 2

If your streams are retrieving data from the same server with a very slow connection (which means that limited bandwidth is a major factor), you will not get anything using multiple streams.

OTOH it might indeed be better to use a single thread for all data sampling operations:

• After a while, you will receive some of the data so that you can update the interface. The acquisition will share the bandwidth in parallel, and you will get a long time without any data, and as a result, the results will appear shortly after another. Your user interface will look less responsive.

• If the selected calls cause a lot of I / O on the server, running them at the same time cannot lead to more bandwidth. Please note that other operations will also be performed on the server.

IMHO you should save the selections in the stream for better responsiveness of the user interface, but use only one.

Edit: You indicate in a comment that the samples may take different time intervals. If you can evaluate which queries will be the fastest to complete the transfer, complete them first. It is still assumed that data transfer takes most of the time, rather than executing a request on the server.

If you cannot estimate how many long requests will be required, or if bandwidth is not the only limitation, using multiple threads may, of course, work best for you.

Answer 3

A typical reason you want to do this is that each of the 5 database queries was long, and some increase in performance was needed when running in parallel. If you do not get a performance boost from this, I just use one thread to work with the database and report its progress in the user interface.

If there is a performance advantage for parallel processing, I would use the built-in thread pool (System.Threading.ThreadPool.QueueUserWorkItem). If you don't need control over the threads, the thread pool is perfect for fire and forget operations. You queue the operation and it calls the delegate when it is done.

Answer 4

Not knowing the specifics, it leaves a bad taste in my mouth. I'm sure it works. Whether this is dangerous depends on whether your main class is thread safe or not. It looks like you need to do a lot of testing again: what happens when two requests complete at the same time that both are doing something with the same data.

Honestly, if I were looking for a “different way” to do this, I would probably skip multithreaded access to the database, I have one thread that does all the work with the database, while the worker threads do some extra the work that should happen (if any), and then reported to the main thread to access the database.

Answer 5

Instead of using simple streaming processing to develop parallel software, please also consider the parallelism problem.

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 on basic parallel programming, as well as links to other articles on the background of parallelism. Examples are included in C # .Net. In addition, he describes a lightweight parallel structure for working with tasks. Opposite to some other structures, this one is very light and very easy to use. After reading these articles, you can write code using parallelism.

Regards, Martine