Measuring performance for async vs sync code in ASP.NET Core

I'm trying to find a scenario where the async function is faster than the synchronous one but I'm having trouble, is there something I'm getting wrong or misunderstanding?

Yes. Async code is not supposed to be faster. It's supposed to require fewer threads. If you had, say 10000 concurrent requests you might see resource (memory, handles, threads, CPU) exhaustion in the synchronous process. But your database is going to fall over way before this happens.

In EF async data access is there not to improve performance of the data access per se, but to conform to the requirements of application frameworks that prefer Async IO, like desktop applications with a single UI thread, or web applications using ASP.NET Core.

Async in EF also occasionally useful to send multiple queries in parallel or to run some other code while you're waiting for a long-running database operation.

Here's a synthetic demo that for a large number of concurrent calls, async can be much faster when the thread pool is smaller than the number of concurrent requests.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;

namespace AsyncDemo
{
    class Program
    {
        static async Task DoStuffAsync()
        {
            await Task.Delay(20);
        }

        static void DoStuffSync()
        {
            Thread.Sleep(20);
        }
        static void Main(string[] args)
        {
            var sw = new Stopwatch();
            List<Task> tasks;
            var iterations = 1000 * 5;

            while (true)
            {
                sw.Restart();
                tasks = new List<Task>();
                for (int i = 0; i < iterations; i++)
                {
                    tasks.Add(Task.Run(() => DoStuffSync()));
                }
                Task.WaitAll(tasks.ToArray());
                Console.WriteLine($"{iterations} sync calls {sw.ElapsedMilliseconds}ms {Process.GetCurrentProcess().Threads.Count} threads");

                tasks = new List<Task>();
                sw.Restart();
                for (int i = 0; i < iterations; i++)
                {
                    tasks.Add(DoStuffAsync());
                }
                Task.WaitAll(tasks.ToArray());
                Console.WriteLine($"{iterations} async calls {sw.ElapsedMilliseconds}ms {Process.GetCurrentProcess().Threads.Count} threads");

            }

        }
    }
}

outputs

5000 sync calls 5546ms 35 threads
5000 async calls 29ms 35 threads
5000 sync calls 3951ms 51 threads
5000 async calls 38ms 51 threads
5000 sync calls 3481ms 52 threads
5000 async calls 29ms 52 threads
5000 sync calls 3320ms 53 threads
5000 async calls 34ms 53 threads
5000 sync calls 3259ms 53 threads
5000 async calls 32ms 53 threads
5000 sync calls 3253ms 53 threads
5000 async calls 33ms 53 threads
5000 sync calls 3321ms 53 threads
5000 async calls 31ms 53 threads
5000 sync calls 3275ms 53 threads
5000 async calls 33ms 53 threads
5000 sync calls 3259ms 51 threads
5000 async calls 28ms 51 threads

From the comments it looks like you are aware that async allows a web server to remain more responsive by not explicitly waiting for responses rather than making queries faster. In a side by side example between a synchronous and asynchronous call, the asynchronous call will be marginally slower because of the setup needed to hand off and resume. So there won't be a performance test you can run to show any improvement. Async is about making the server more responsive to load.

Consider a web server has 100 worker threads handling requests. Every time a user connects to your site, refreshes a page, etc. they get handed off to a worker. At any one point in time, 100 requests can be processed. If a 101th request comes in while all other threads are busy processing requests, one of two things could happen: An extra thread is allocated (competing for resources and time with the other 100, and time is needed to allocate extra threads) or the request waits for a thread to be freed. Since you'll probably have a few request actions that might take a second or so, the majority of requests will be maybe a couple of milliseconds to run, so overall this is only a noticeable issue when there are considerably more than 100 concurrent requests as they sit waiting in order for a worker.

Let's say there is a particular report that results in some pretty beefy SQL calls across millions of rows and a great # of tables. For instance an EOFY type report or quarterly report. These reports won't typically be run that often, but when they are, it can be by a significant # of people at a particular time. (First week of financial year for example) Each query takes 10+ seconds to run, The DB itself can only handle so many requests so the first queries might be 10 seconds, but block others so on average it might push 30 or even 60 seconds. Now when 100 requests come in, each of those threads is tied up for 30+ seconds. Your site becomes unresponsive as threads are exhausted. Async queries help accommodate this. Requests will still take ~30s, but the web server frees up the listening worker thread so it can respond to other requests, many of which will not be triggering the report, but would be caught up waiting when the threadpool was filled.

So, to actually observe something like this, you need a particularly expensive operation, fix the number of available worker threads, and initiate a load test with in excess of those # of requests. For instance, limiting the web server to 5 threads, kick off 5 expensive requests, then kick off an additional "cheap" request and measure that cheap request's responsiveness. With Sync code, that cheap request would be left waiting for one of the threads to be freed up. With Async code that last request would be actioned considerably faster. Your attempts so far look to be a bit flawed because you have all requests actioning the same call and expecting a performance boost. It won't, it is about smaller, simpler requests getting hung up waiting for longer ones to complete. If your query is too simple (fast) there's nothing to observe. You need a query that takes several seconds to run, then mix that with requests that normally take a couple ms.

Async is not a silver bullet for website performance. It can be tougher to debug and it's inherently a bit slower. Often you will see examples that use it everywhere for consistency, or simplistic examples where it honestly provides no benefit. My general advice is to use synchronous calls by default and save async calls for notably expensive operations. For instance, loading an individual record by ID I will aim to keep synchronous, but searches, especially involving text matching or involving non-indexed columns or complex associations, I will make async. Often there will be examples that require different implementations that I wouldn't even rely on async. Such as the example above with an EOFY type report that could take several seconds to run: Something like that I would look to implement a Queue for report requests handled by an explicit background worker (or pool) and use a DBContext pointed at a reporting replica to help ensure that too many requests aren't run in parallel, and don't block read-write access to the main application database. Simply slapping async on an expensive query won't cut it if there is a possibility that the majority of requests could trigger that operation at the same time. You may free up your web server thread pool, but still cripple the site with a bottleneck further down the chain.