Process thousands of database calls simultaneously

Question

This little console app is a proof of concept for making thousands of database calls. The idea is that we want many calls to be happing simultaneously. No need to wait for one call to finish before starting the next.

At first, this (see below) seemed like a good approach, but when we applied this to the actual database calls, what we're seeing is that it seems to stack the processes. Meaning, it Starts all of them, but none of them Finish until all of them have been started.

I would expect (and want) some of the calls to be finishing before others have started. But that doesn't seem to be the case.

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("starting");
        DatabaseCallsAsync().Wait();
        Console.WriteLine("ending"); // Must not fire until all database calls are complete.
        Console.Read();
    }

    static async Task DatabaseCallsAsync()
    {
        List<int> inputParameters = new List<int>();
        for (int i = 0; i < 100; i++)
        {
            inputParameters.Add(i);
        }
        await Task.WhenAll(inputParameters.Select(x => DatabaseCallAsync($"Task {x}")));
    }

    static async Task DatabaseCallAsync(string taskName)
    {
        Console.WriteLine($"{taskName}: start");
        await Task.Delay(1000);
        Console.WriteLine($"{taskName}: finish");
    }
}

How can this be adjusted so that some calls finish without waiting for all of them to start?

Answer 1

It's important to note a couple things:

1. async methods start running synchronously. The magic happens at await, if await is given an incomplete Task.
2. Asynchronous != parallel. Running something asynchronously just lets the thread go and do something else while it is waiting for a reply from somewhere. It doesn't mean that multiple things are happening at the same time.

With those things in mind, this is what's happening in your case when it loops through all the tasks you've created:

1. All of the tasks are put on the "to do" list.
2. Task 1 is started.
3. At await, an incomplete Task is returned, and the rest of the method is put on the "to do" list.
4. The thread realizes there is nothing to do and moves on to the next thing on the "to do" list, which is to start the next Task.

At step 4, the next thing on the "to do" list will always be the next Task in the list until there is nothing left in the list. Only then, the next thing on the "to do" list is the continuation of the tasks that have completed, in the order they completed.

All of this happens on the same thread: it is asynchronous, not parallel.

But! If you actually use SQL calls (and you make a new connection for each task, since a single connection can only run one query at a time - unless you enable Multiple Active Result Sets) and monitor SQL, you will see those calls coming in and likely finishing before all of them have started, because SQL runs queries in parallel. It's only that the continuation of the C# methods won't start until all the tasks have started.

If you are truly looking to run these in parallel, then you need multi-threading. You can look at Parallel.ForEach (examples here), but that is not asynchronous. It will create a thread for each instance and the thread will block until it's complete. That's not a big deal in a desktop app, but in ASP.NET, threads are finite, so you need to be careful.

There is a big discussion of this here, but I particularly like this answer, which is not multi-threading, but gives a way to throttle your tasks. So you can tell it to start x number of tasks, and as each task finishes, start the next until all of them have run. For your code, that would look something like this (running 10 tasks at a time):

static async Task DatabaseCallsAsync()
{
    List<int> inputParameters = new List<int>();
    for (int i = 0; i < 100; i++)
    {
        inputParameters.Add(i);
    }

    await RunWithMaxDegreeOfConcurrency(10, inputParameters, x => DatabaseCallAsync($"Task {x}"));
}

static async Task DatabaseCallAsync(string taskName)
{
    Console.WriteLine($"{taskName}: start");
    await Task.Delay(1000);
    Console.WriteLine($"{taskName}: finish");
}

public static async Task RunWithMaxDegreeOfConcurrency<T>(
     int maxDegreeOfConcurrency, IEnumerable<T> collection, Func<T, Task> taskFactory)
{
    var activeTasks = new List<Task>(maxDegreeOfConcurrency);
    foreach (var task in collection.Select(taskFactory))
    {
        activeTasks.Add(task);
        if (activeTasks.Count == maxDegreeOfConcurrency)
        {
            await Task.WhenAny(activeTasks.ToArray());
            //observe exceptions here
            activeTasks.RemoveAll(t => t.IsCompleted); 
        }
    }
    await Task.WhenAll(activeTasks.ToArray()).ContinueWith(t => 
    {
        //observe exceptions in a manner consistent with the above   
    });
}

Answer 2

Your delay is to high to get the expected result. All Requests are started before the first one is finished. This depends on your System.

await Task.Delay(50);

Worked for me.

Answer 3

To get the effect i think you're after i used:

    static async Task DatabaseCallsAsync()
    {
        List<Task> inputParameters = new List<Task>();
        Parallel.For(0, 100, (i) => inputParameters.Add(DatabaseCallAsync($"Task {i}")));
        await Task.WhenAll(inputParameters);
    }

    static async Task DatabaseCallAsync(string taskName)
    {
        Console.WriteLine($"{taskName}: start");
        await Task.Delay(50);
        Console.WriteLine($"{taskName}: finish");
    }

BUT If you're gonna make 4151 requests then you should probably extend parallel.for with ParallelOptions

For a non Parallel approach i used:

    static async Task DatabaseCallsAsync()
    {
        List<Task> inputParameters = new List<Task>();
        for (int i = 0; i < 100; i++)
        {
            inputParameters.Add(DatabaseCallAsync($"Task {i}"));
        }
        await Task.WhenAll(inputParameters);
    }

However, doing it this way i had to set Task.Delay to 10 because starting the work for 100 was done faster than 50 milliseconds(Initial Task.Delay)