Controlling Number of Backgroundworkers

curlydog

Well-known member
Joined
Jul 5, 2010
Messages
50
Programming Experience
Beginner
I have an application which queries a number MySQL databases, up to 9 depending on the user's selection. The databases all sit on the same server. Because the application can make many many calls to the server, I'm trying to speed things up by creating multiple concurrent connections to the server.

Essentially I have a "Subject" object, each subject has an email address which is used to query the databases and determine where the email address appears. Rather than query the first database, then the second, then the third etc. I have created a background worker for each of the databases queries so that, in theory, the queries can all be run at the same time.

It is working with limited success. By monitoring the connections I can see that multiple connections are being made and my processing speed increases significantly. The problem is the number of connections increases, out of control, until too many are running for the MySQL server to handle.

The problem, I think, arises because I can be processing very large numbers of subjects in one go. The subjects are created in a for loop iterating through a list of email addresses, one subject per email. The list of emails can be very long, in the order of tens of thousands.

I think what is happening is that as each subject is created, the background workers each start their queries (up to nine at one), creating 9 connections. Because these connections are made on separate threads, the main body of the processing moves to the next subject and opens up another 9 connections, before all of the first 9 have been closed. So for example, if only 4 for the first 9 have completed and closed, I now have 13 open connections. This cascades until I have too many connections open and the server starts sending back errors.

What I'm trying to do is created a subject, query the nine databases all at once using background workers and once all of the background workers have finished their queries, move on to creating the next subject.

I'm a little confused how to control this. I've tried using .isbusy and runworkercompleted, but without success so far. I think the problem I'm having is the structure of my application. The list of initial emails is looped through, on the submission form presented to the user. For each email in the list "Subject" object is created. During the creation of this object, the calls to the databases are made. Really I need control the creation of the subject objects by waiting until the previous subject has been created.

Does this make sense?

Thanks for any help or suggestions
 
What version of VS are you using for this?

I can think of two ways to handle it, either have your program handle 1 Subject at a time (with the up to 9 connections) or have it do something like 5 (or whatever number is optimal for you) Subjects at a time, but when a Subject is processing it does things sequentially (not 5 Subjects at a time with 9 connections each). I know in the newer versions of VS (I think it was added in .Net 4.5) there's the new Tasks objects (System.Threading.Tasks) which is threaded and is meant to allow 1 or more tasks to be running at a time which may give you an easy way to have, say, 5 Subjects running at a time and when 1 Subject finishes it fires off the next Task (the next Subject) from the queue until they all complete, I think it even allows you to add more tasks to the queue while it's running, I'm not sure though, haven't actually used Tasks yet.
 
What version of VS are you using for this?

I can think of two ways to handle it, either have your program handle 1 Subject at a time (with the up to 9 connections) or have it do something like 5 (or whatever number is optimal for you) Subjects at a time, but when a Subject is processing it does things sequentially (not 5 Subjects at a time with 9 connections each). I know in the newer versions of VS (I think it was added in .Net 4.5) there's the new Tasks objects (System.Threading.Tasks) which is threaded and is meant to allow 1 or more tasks to be running at a time which may give you an easy way to have, say, 5 Subjects running at a time and when 1 Subject finishes it fires off the next Task (the next Subject) from the queue until they all complete, I think it even allows you to add more tasks to the queue while it's running, I'm not sure though, haven't actually used Tasks yet.

Thanks for the suggestion. I'm looking at Tasks and think they may be a solution to my problem.

I'm struggling to figure out the logic that would allow me to control the number of tasks running concurrently and starting new tasks when previous ones have completed.
 
Back
Top