Async vs Sync Performance in ASP.NET Web API: Network Call vs ODP Oracle Connection

[CavidH]

I'm working on an ASP.NET Web API project and recently performed some performance tests comparing asynchronous (async) and synchronous (sync) calls in two scenarios:

1. Network Call: An external API call.
2. ODP Oracle Connection: Database operations using Oracle Data Provider for .NET (ODP.NET).

Here's what I observed:

For network calls, the async implementation showed better performance compared to the sync implementation. For ODP Oracle connections, both async and sync implementations exhibited similar performance.

public async Task<ApiTestResponseDto> NetworkCallAsync(string url)
{ 
    using HttpClient client = new HttpClient() { BaseAddress = new Uri(url) };
    client.DefaultRequestHeaders.Add("user-agent", "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; WOW64; Trident/6.0)");
    var result = await client.GetAsync(client.BaseAddress);
    var content = await result.Content.ReadAsStringAsync();
    return new ApiTestResponseDto { ResultString = $"Async network call finished" };
}

public ApiTestResponseDto NetworkCall(string url = "https://www.nytimes.com/international/")
{
    using WebClient client = new() { BaseAddress = url };
    client.Headers.Add("user-agent", "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; WOW64; Trident/6.0)");
    var result = client.DownloadString(client.BaseAddress);
    return new ApiTestResponseDto { ResultString = $"Sync network call finished" };
}

public async Task<IActionResult> GetDataAsync()
{
    using (OracleConnection connection = new OracleConnection(connectionString))
    {
        await connection.OpenAsync();
        using (OracleCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM my_table";
            using (OracleDataReader reader = await command.ExecuteReaderAsync())
            {
                // Process data
            }
        }
    }
    return Ok();
}

public IActionResult GetDataSync()
{
    using (OracleConnection connection = new OracleConnection(connectionString))
    {
        connection.Open();
        using (OracleCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM my_table";
            using (OracleDataReader reader = command.ExecuteReader())
            {
                // Process data
            }
        }
    }
    return Ok();
}

Additional Information

As we know, ODP.NET has added new async methods to improve performance. You can find more details about these async methods in the ODP.NET documentation.

Questions:

1. Why does the async implementation perform better than the sync implementation for network calls but not for ODP Oracle connections?
2. Are there any best practices or considerations I should be aware of when deciding between async and sync for database operations in ASP.NET Web API?
3. Could there be specific configurations or optimizations in ODP.NET that I might be missing to leverage async operations effectively?

Any insights or advice would be greatly appreciated. Thank you!

[alexkeh]

1. Why does the async implementation perform better than the sync implementation for network calls but not for ODP Oracle connections? In your ODP.NET connection scenario, you create a command object and set the command text. Those are fast operations for .NET to perform and will execute quickly. Thus, you will not see much difference vs. the longer time spent creating a connection and the connection pool, which is what ODP.NET does upon the first Open() call.

You can try the sample code here to ensure a more time consuming operation occurs between the open and execute operations: https://github.com/oracle/dotnet-db-samples/blob/master/samples/async/async.cs

2. Are there any best practices or considerations I should be aware of when deciding between async and sync for database operations in ASP.NET Web API?

MS has some general advice here: https://learn.microsoft.com/en-us/dotnet/csharp/asynchronous-programming/async-scenarios

If you extrapolate to ODP.NET and DB scenarios, async is useful for long running operations when don't need the DB results to continue your other .NET operations. For example, you could have the DB execute a long running query or stored procedure. Or you could create a connection pool with a large minimum pool size. While the app is waiting, it could perform other operations on the client side.

3. Could there be specific configurations or optimizations in ODP.NET that I might be missing to leverage async operations effectively?

Programmatically, you're using async correctly. It's just the benefits can vary depending on the situation. Besides using async for long running operations that are IO and/or CPU bound, you can see increasing async benefits as load increases on your app. With more load, async better ensures fewer operations are blocked by long running operations, which tend to get longer as the load increases.

[alexkeh]

I believe this issue is resolved via the answers to the questions. Closing the issue.