How to use Multi threading to call stored procedure for each of item in collection

Question

Have a job which does the following 2 tasks

1. Read up to 300 unique customerId from database into a List.
2. Then Call a stored procedure for each customerId, which execute queries in the SP, creates a XML (up to of 10 KB) and store the XML into a database table.

So, in this case there should be 300 records in the table.

On an average, the SP takes around 3 secs to process each customer until it's xml creation. So that means, it's taking total 15 minutes to completely process all 300 customers. The problem is, in future it may be even more time-consuming.

I don't want to go with bulk-insert option by having logic of xml creation in application. Using bulk-insert, I won't be able to know which customerId's data was a problem if xml creation failed. So I want to call the SP for each customer.

To process all customer in parallel, I created 4 dedicated threads, each processing a collection of unique customerId, all 4 threads together processed all 300 customers in 5 minutes. Which I was expecting.

However I want to use ThreadPool rather than creating my own threads.

I want to have 2 type of threads here. One type of is process and create xml for each customer, another to work on the customers for XML is already created. This another thread will call a SP which would update a flag on a customer table based on customer's XML available.

So which is the best way to process 300 customers in parallel and quickly and also updating customer table in parallel or on a separate thread?

Is dedicated thread still good option here or Parallel.ForEach or await Task.WhenAll?

I know Parallel.Foreach will block the main thread, which I want to use for updating customer table.

Answer 1

You have to choose among several options for implementation. First of all, choose the schema you are using. You can implement your algorithm in co-routine fashion, whenever the thread needs some long-prepairing data, it yields the execution with await construction.

// It can be run inside the `async void` event handler from your UI.
// As it is async, the UI thread wouldn't be blocked
async Task SaveAll()
{
    for(int i = 0; i < 100; ++i)
    {
         // somehow get a started task for saving the (i) customer on this thread
        await SaveAsync(i);
    }
}

// This method is our first coroutine, which firstly starts fetching the data
// and after that saves the result in database
async Task SaveAsync(int customerId)
{
    // at this point we yield the work to some other method to be run
    // as at this moment we do not do anything
    var customerData = await FetchCustomer(customerId);
    // at this moment we start to saving the data asynchroniously
    // and yield the execution another time
    var result = await SaveCustomer(customerData);
    // at this line we can update the UI with result
}

FetchCustomer and SaveCustomer can use the TPL (they can be replaced with anonymous methods, but I don't like this approach). Task.Run will execute the code inside the default ThreadPool, so UI thread wouldn't be blocked (more about this method in Stephen Cleary's blog):

async Task<CustomerData> FetchCustomer(int customerId)
{
    await Task.Run(() => DataRepository.GetCustomerById(customerId));
}

// ? here is a placeholder for your result type
async Task<?> SaveCustomer(CustomerData customer)
{
    await Task.Run(() => DataRepository.SaveCustomer(customer));
}

Also I suggest you to examine this articles from that blog:

• StartNew is Dangerous
• Async and Await
• Don't Block on Async Code
• Async/Await - Best Practices in Asynchronous Programming

Another option is to use the TPL Dataflow extension, very similar to the this answer:

Nesting await in Parallel foreach

I suggest you to examine the contents of linked post, and decide for yourself, which approach will you implement.

Answer 2

1. I would try to solve your task completely within SQL. This will greatly reduce server roundtrips.

2. I think you can create a enumeration of tasks, each one dealing with one record, and then to call Task.WhenAll() to run them all.