Have a job which does the following 2 tasks
1 : Read upto 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 thats mean, it's taking total 15 minutes to completly process all 300 customers. The problem is, in future it may
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'data as problem when xml creation failed. So i want to call the SP for each customer.
to process all customer parallely, SO i created 4 dedicated threads, each processing a collection of unique customerID, all 4 threads together processed all 300 customers in 5 minutes only. 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 parallely and quickly and also updating customer table parallely or on a separate thread,
Is deadicated 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.
Please advise a solution.