Horrible performance using SqlCommand Async methods with large data

by

On a system without significant load, an async call has a slightly bigger overhead. While the I/O operation itself is asynchronous regardless, blocking can be faster than thread-pool task switching.

How much overhead? Let’s look at your timing numbers. 30ms for a blocking call, 450ms for an asynchronous call. 32 kiB packet size means you need you need about fifty individual I/O operations. That means we have roughly 8ms of overhead on each packet, which corresponds pretty well with your measurements over different packet sizes. That doesn’t sound like overhead just from being asynchronous, even though the asynchronous versions need to do a lot more work than the synchronous. It sounds like the synchronous version is (simplified) 1 request -> 50 responses, while the asynchronous version ends up being 1 request -> 1 response -> 1 request -> 1 response -> …, paying the cost over and over again.

Going deeper. ExecuteReader works just as well as ExecuteReaderAsync. The next operation is Read followed by a GetFieldValue – and an interesting thing happens there. If either of the two is async, the whole operation is slow. So there’s certainly something very different happening once you start making things truly asynchronous – a Read will be fast, and then the async GetFieldValueAsync will be slow, or you can start with the slow ReadAsync, and then both GetFieldValue and GetFieldValueAsync are fast. The first asynchronous read from the stream is slow, and the slowness depends entirely on the size of the whole row. If I add more rows of the same size, reading each row takes the same amount of time as if I only have one row, so it’s obvious that the data is still being streamed row by row – it just seems to prefer to read the whole row at once once you start any asynchronous read. If I read the first row asynchronously, and the second synchronously – the second row being read will be fast again.

So we can see that the problem is a big size of an individual row and/or column. It doesn’t matter how much data you have in total – reading a million small rows asynchronously is just as fast as synchronously. But add just a single field that’s too big to fit in a single packet, and you mysteriously incur a cost at asynchronously reading that data – as if each packet needed a separate request packet, and the server couldn’t just send all the data at once. Using CommandBehavior.SequentialAccess does improve the performance as expected, but the massive gap between sync and async still exists.

The best performance I got was when doing the whole thing properly. That means using CommandBehavior.SequentialAccess, as well as streaming the data explicitly:

using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
  while (await reader.ReadAsync())
  {
    var data = await reader.GetTextReader(0).ReadToEndAsync();
  }
}

With this, the difference between sync and async becomes hard to measure, and changing the packet size no longer incurs the ridiculous overhead as before.

If you want good performance in edge cases, make sure to use the best tools available – in this case, stream large column data rather than relying on helpers like ExecuteScalar or GetFieldValue.

More Related Contents:

  1. How and when to use ‘async’ and ‘await’
  2. If async-await doesn’t create any additional threads, then how does it make applications responsive?
  3. HttpClient.GetAsync(…) never returns when using await/async
  4. Should I avoid ‘async void’ event handlers?
  5. Running multiple async tasks and waiting for them all to complete
  6. How do yield and await implement flow of control in .NET?
  7. Fire and forget async method in asp.net mvc
  8. Task sequencing and re-entracy
  9. Enforce an async method to be called once
  10. Why use Async/await all the way down
  11. Async and await – difference between console, Windows Forms and ASP.NET
  12. Different forms of the WCF service contract interface
  13. How does async works in C#?
  14. Why use async and return await, when you can return Task directly?
  15. Executing tasks in parallel
  16. Should I worry about “This async method lacks ‘await’ operators and will run synchronously” warning
  17. Proper way to start and fire-and-forget asynchronous calls?
  18. Use Task.Run() in synchronous method to avoid deadlock waiting on async method?
  19. A pattern for self-cancelling and restarting task
  20. Async always WaitingForActivation
  21. What is the correct way to cancel an async operation that doesn’t accept a CancellationToken?
  22. A pattern to pause/resume an async task?
  23. Async thread body loop, It just works, but how?
  24. Converting async/await to Task.ContinueWith
  25. How to Async Files.ReadAllLines and await for results?
  26. Async and Await with For Loop [duplicate]
  27. Await and SynchronizationContext in a managed component hosted by an unmanaged app
  28. When is the best place to use Task.Result instead of awaiting Task
  29. Timeout pattern on task-based asynchronous method in C#
  30. Await in catch block

Leave a Comment