Parllel Foreach vs Async Foreach for DB calls

Question

I have scenario where I have to call a same database stored procedure for each item in a list. I don't want to use foreach as it will degrade the performance, Which will be best option parllel foeach or async/await foreach?

Below is sample code

public Task<List<object>> CallMethod()
{
    foreach(var obj in input)
    {
        List.Add(await Task.Run(() =>CallDatabase(obj)));
    }
   return List;
}

public CallDatabase(object)
{
    //Code to call DB 
}

All the objects received from the DB are independents.

After few research I am planning to use async calls, will this improve performance?

Answer 1

I'm not sure it's going to make any difference either way. I assume you still need to wait for all results to be loaded, in which case async does not help, and your bottleneck will most likely be network I/O and server processing rather than local CPU, so parallelism will not help either.

That said, if you don't need the results of the query and don't care if there is an error, then async may help in a fire-and-forget scenario.

Your biggest bang for your buck may be to try and get multiple results in one query rather than firing off a bunch of individual queries.

Answer 2

Defintiely Async, as Parallel.ForEach is meant for compute intensive operations. It spreads over available core resources and orchestrates them accordingly. Async, instead, is meant for just this kind of operations: make a request to the service, go ahead and receive notification once resources requested before are available.

Answer 3

This is mostly comment to D Stanley's answer - switching to parallel/async code unlikely to improve performance.

If your main concern is responsiveness/scalability - async would be better as generally DB access is IO-bound operation. It also allows to pick between sequential and parallel processing (i.e. in case your DB layer does not support concurrent requests on same connection for some reason). Additionally with async it is easier to get synchronization right for updating UI/request if you use default synchronization context.

Sequential: it will run about as long as non-async solution, but the thread will be free to perform other activities at the same time (for UI applications like WinForms/WPF) or process requests (ASP.Net).

async public Task<ResultType> CallMethodAsync()
{
    foreach(var obj in input)
    {
        var singleResult = await CallDatabaseAsync(obj);
        // combine results if needed
    }
    // return combined results    
}

Parallel: will run all requests at the same time, will likely be faster than sequential solution.

async public Task<ResultType> CallMethodAsync()
{
    List<Task<SingleResultType>> tasks = new List<Task<SingleResultType>>();
    foreach(var obj in input)
    {
        tasks.Add(await CallDatabaseAsync(obj));
    }
    await Task.WhenAll(tasks);

    foreach(SingleResultType result in tasks.Select(t=>t.Result))
    {
        // combine results if needed
    }
    // return combined results    
}

Note that async generally requires all your code to be asynchronous - so if you converting just small piece of code to run in parallel Parallel.ForEach may be easier solution as it does not involve dealing with await vs Task.Wait - Deadlock?.

Answer 4

I have implemented a solution; not sure if this async or will improve performance a bit or not, I am very new to async so don't have much idea.

 public static async Task<List<Response>> Execute(Request objInput)
 {
       List<Response> objList = new List<Response>();
       foreach (object obj in objInput.objs)
        {
            objList.Add(await Task.Run(() =>GetDataFromDB(obj)));
        }
  return objList;
 }


  private static object GetDataFromDB(object obj)
  {
       //Call DB and build the object
  }

If this is not the correct way to implement async, then please offer other ideas.