Question Multithreading with databases - Best practice?

[dbridle]

I have read many articles on multithreading over the past few days and so far I'm fairly comfortable with how it works. I am now working on my actual application, and have come across a few design issues that I'm hoping somebody has experience with.

The basic premise of the application is simple. A database filled with files names and a "converted" field that has a 1 or a 2 in it (1 for unconverted, 2 for converted). The application loops through every record where "converted" = 1, retrieve the file and convert it.

My single threaded application works fine, except its far too slow. Due to the huge amount of rows in the database (4 million+) and the time constraints on the project, I have decided to run as many threads as the hardware can allow to cut down on the timings.

So, the dilemma is accessing database records and which threads should grab which record and how. I have been thinking of all kinds of wierd and wacky ways to do this (Thread 1 - Odd number id's, thread 2, even id's, etc) but I'm thinking there must be a correct way to go about this, but so far, I have not uncovered anything that might guide me the right way.

Any help would be very much appreciated.

Kindest regards

- DBridle

 

[jmcilhinney]

You should be using the ThreadPool for this. It will optimise the number of simultaneous threads. You simply loop through ALL the rows and call QueueUserWorkItem once for each one. The system will then decide how many tasks to run simultaneously and the others will sit in the queue until a thread becomes available.

 

[dbridle]

Thanks for the advise, I will check out Thread Pools!

- DBridle

 

[helderjsd]

If I have understand correctly your question, I'll try give you a solution.

In the past, I have some similar, but not with database tables. I'm using files. The solution is the same.

In my case, I use a fixed number of threads (12) for doing the job.

I has a trouble, is attrib each file to a one and only to a one thread.

The solution I found, it's creta e function wich returns me the next file.

Then, each loop in each thread, calal the function, and obtain the name of the next file to be processed.

I don't know if I was clear..