SqlDataReader .ReadAsync() vs .Read()

Test Conditions:

.NET Core 2, Console Application

SQL Server 2008 R2: Yes, this needs updating but there is still important information here including async all the way isn't necessarily the best idea for every situation.

Test Application Code:

4 x 100 paralleled queries, 1000 records each query.

       static void Main(string[] args)
       {
           var repeat = 4;
           var parallel = 100;
           var time = new ConcurrentBag<TimeSpan>();
           var totalTime = Stopwatch.StartNew();
           for(var x = 0; x< repeat; x++)
           {
               Console.Write("{0}) ", x);
               Parallel.For(0, parallel, i =>
               {
                   var sw = Stopwatch.StartNew();
                   // Query code goes here. Example: SELECT TOP 1000 * FROM Product
                   time.Add(sw.Elapsed);
               });
               Console.WriteLine("complete", x);
           }
           Console.WriteLine();
           Console.WriteLine("Total Time: {0}", totalTime.Elapsed);
           Console.WriteLine("Average Query: {0}",
time.Aggregate((a, b) => a + b) / parallel / 4);
           Console.WriteLine();
           Console.WriteLine("Press ENTER to continue.");
           Console.ReadLine();
       }

Results:

Baseline

NOTE: The CommandBehavior.Default versions of the following tests were only slightly slower on all metrics.

.Open()
.ExecuteReader(CommandBehavior.SequentialAccess)
while(reader.Read())
reader.GetValues(array)

Total Time: 00:00:25.1435656
Average Query: 00:00:00.6731697

await .OpenAsync()
await .ExecuteReaderAsync(CommandBehavior.SequentialAccess)
while(reader.Read())
reader.GetValues(array)

Total Time: 00:00:30.2188467
Average Query: 00:00:01.4122918

await .OpenAsync()
await .ExecuteReaderAsync(CommandBehavior.SequentialAccess)
while(await reader.ReadAsync())
reader.GetValues(array)

Total Time: 00:00:32.6702872
Average Query: 00:00:02.6879162

Assessment

Again, this is a SQL Server 2008 R2 instance but the obvious evidence here is:

The fully asynchronous approach not only takes longer in total time and per query, but uses up a significant amount more of CPU time.

This suggests that for operations like extracting data, using async methods within iterations should be scrutinized heavily.