Multithreaded Search using c#

Question

Hi All

I want to seach more than 15000 values in a select statement as shown below:

select * from tableA where id in (1,2,3......16000)

Can I use threads, say around 3, and partion 15000 values in diffrent select statement.

1. select * from tableA where id in (1,2,3......5000)
2. select * from tableA where id in (5001....10000)
3. select * from tableA where id in (10001....15000)

and run these 3 select statment in parallel.

Answer 1

That is possible and may even be a good idea since sending a (very) large IN statement to a database may result in errors.

Your database should handle your parallel queries correctly and without problems.

Answer 2

Yes, but the real question is why?

Something like this might get you started:

var itms = new List<YourDataClass>();

var thr1 = new Thread(new ThreadStart(delegate()
{
    // select code
    // populate itms
}));
var thr2 = new Thread(new ThreadStart(delegate()
{
    // select code
    // populate itms
}));
var thr3 = new Thread(new ThreadStart(delegate()
{
    // select code
    // populate itms
}));

thr1.Start();
thr2.Start();
thr3.Start();

However, that said, if your IDs are integers and (based on your sample) the range of IN values are sequential, you might want to switch to a where id > 1 and id < 16000 style. This may yeild better performance.

Answer 3

It might be worth to create a temp table and join it.

Answer 4

You may tried the parallel programming feature of C# 4.0

It's rather simple:

List<String> jobs = new List<String>();
Parallel.ForEach(jobs, job=>
    {
        Foo(job);
    }
);

Have a look at this: http://msdn.microsoft.com/en-us/library/dd460720.aspx