Optimising your DAL with async ADO.NET

Andrew Bullock     @trullock

Overview

• Problems with Sync

• Sync vs Async Syntax

• (Non)Sequential Access

• TDS Protocol

• Best Practices / ProTips

public async Task CallAsyncThing() {
    
    // 1. We enter this method

    // 2. We call this method
    var task = DoThingAsync();

    // 5. We are here, without
    //    waiting 1000ms
    
    // We wait (non blocking) for the 
    // task to complete (it may already have)
    await task;

}

public Task DoThingAsync() {

    // 3. We enter this method

    // 4. We return a task which will complete
    //    in 1000ms
    return Task.Delay(1000);

}

Prerequisite

Async  + Await

public void CallSyncThing() {
    
    // 1. We enter this method

    // 2. We call this method
    DoThingSync(); 

    // 4. We are here after 
    //    DoThingSync() has finished

}

public void DoThingSync() {

    // 3. We block the thread for 1000ms
    Thread.Sleep(1000); 

}

Problems with Sync

• Lazy

• Inefficient

• Blocking

• Less Cool than Async

if(reader.Read())
{
	var user = new User
	{
		Id = reader.GetGuid(0)
	};

        var photo = reader.GetBinary(3); // Massive field

        user.Age = reader.GetInt32(2);
        user.Name = reader.IsDBNull(1) ? null : reader.GetString(1);
        user.Photo = photo;

        return user;
}

Problems with Sync

• Inefficient + Lazy

Problems with Sync

• Thread Blocking

Problems with Sync

• Less Cool than Async

Sync

Async

using (var connection = new SqlConnection("..."))
{
	connection.Open();

	var command = connection.CreateCommand();
	command.CommandText = @"
select 
	Id
	, Name
	, Age
from	User
	";

	using (var reader = command.ExecuteReader())
	{
		if(reader.Read())
		{
			return new
			{
				Id = reader.GetGuid(0),
				Name = reader.IsDBNull(1) ? null : reader.GetString(1),
				Age = reader.GetInt32(2)
			};
		}
	}
}

Synchronous ADO.NET Syntax

using (var connection = new SqlConnection("..."))
{
	await connection.OpenAsync();

	var command = connection.CreateCommand();
	command.CommandText = @"
select 
	Id
	, Name
	, Age
from	User
	";

	using (var reader = command.ExecuteReader())
	{
		if(await reader.ReadAsync())
		{
			return new
			{
				Id = reader.GetGuid(0),
				MassiveBlob = reader.IsDBNull(1) ? null : reader.GetString(1),
				Age = reader.GetInt32(2)
			};
		}
	}
}

Asynchronous ADO.NET Syntax

using (var reader = command.ExecuteReader())
{
	while (await reader.ReadAsync())
	{
		var user = new
		{
			Id = await reader.GetFieldValueAsync<Guid>(0),
			Name = await reader.IsDBNullAsync(1) ? null : 
						await reader.GetFieldValueAsync<string>(1),
			Age = await reader.GetFieldValueAsync<int>(2)
		};
	}
}

Asynchronous ADO.NET

Sequential CommandBehaviour

TDS Protocol

When to use what

Always NextResultAsync()

NextResut() vs NextResultAsync()

When to use what

Read() vs ReadAsync()

Probably ReadAsync()

When to use what

IsDBNull and GetFieldValue<T> vs IsDBNullAsync and GetFieldValueAsync<T>

Pro Tips:

• Avoid: NTEXT, TEXT, IMAGE, TVP, UDT, XML, [N]VARCHAR(MAX), VARBINARY(MAX) where possible
   
• Keep the maximum size of variable column as small as possible
   
• Use NULL when you have no data
   
• Understand your packet vs row size
   
• Select large columns last
   
• Stream large data

var outputStream = new MemoryStream();
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
	while (await reader.ReadAsync())
	{
		var stream = reader.GetStream(0);
		await stream.CopyToAsync(outputStream);
	}
}

Asynchronous ADO.NET

Test It Yourself

• Set a low connection pool size:

  "data source=your_server; initial catalog=your_db; max pool size=1"

• Set a low thread pool size:

  <configuration>
  

•   <system.web>
      <applicationPool 
          maxConcurrentRequestsPerCPU="1"
          maxConcurrentThreadsPerCPU="1" />
    </system.web>
  </configuration>

Resources

• https://dpaoliello.wordpress.com/2014/04/22/improving-async-performance-for-sqldatareader/
   
• http://blogs.msdn.com/b/adonet/archive/2012/04/20/using-sqldatareader-s-new-async-methods-in-net-4-5-beta.aspx
   
• http://blogs.msdn.com/b/adonet/archive/2012/07/15/using-sqldatareader-s-new-async-methods-in-net-4-5-beta-part-2-examples.aspx

Thanks

Andrew Bullock         @trullock

myunidays.com/careers