This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET Core. In this post we’ll focus on database round trips in our data access code …

Database round trips are expensive. The bigger the distance between where our data access code is deployed and our deployed database, the more expensive that round trip is. So, we want to keep the trips from our data access code to the database to a minimum.

The example in a previous post on ORMs is a an example of reducing 4 trips to a single trip. This highlights that Dapper gives us fine grain control to really reduce our database round trips.

N+1 Problem

Let’s have a look at a classic problem - the “N+1” problem …

First, here’s our database model. In summary, we have contacts that can have multiple activities stored against them.

Here’s our controller action method for GET /api/contacts that returns a collection of contacts including any associated activities:

[ HttpGet ] public IActionResult GetContactsWithActivities ( ) { IEnumerable < Contact > contacts = null ; using ( SqlConnection connection = new SqlConnection ( _connectionString ) ) { connection . Open ( ) ; contacts = connection . Query < Contact > ( "SELECT ContactId, Title, FirstName, Surname FROM Contact" ) ; foreach ( var contact in contacts ) { contact . Activities = connection . Query < Activity > ( @"SELECT Summary, DateCompleted, Type FROM Activity INNER JOIN ActivityType ON Activity.ActivityTypePK = ActivityType.ActivityTypePK WHERE EXISTS (SELECT * FROM Contact WHERE Contact.ContactPK = Activity.ContactPK AND Contact.ContactId = @ContactId)" , new { ContactId = contact . ContactId } ) ; } } return Ok ( contacts ) ; }

We can see straight away that the endpoint isn’t fast:

If we use SQL profiler, we can see there is a call to get the Contact record and then “N” calls to get the Activity records for each contact - the classic “N+1” problem!

To improve the performance of this endpoint, we can use dappers “multi-mapping” feature to grab and map the data in a single round trip:

[ HttpGet ] public IActionResult GetContactsWithActivities ( ) { IEnumerable < Contact > contacts = null ; using ( SqlConnection connection = new SqlConnection ( _connectionString ) ) { connection . Open ( ) ; var contactDictionary = new Dictionary < Guid , Contact > ( ) ; contacts = connection . Query < Contact , Activity , Contact > ( @"SELECT ContactId, Title, FirstName, Surname, ActivityType.Type, Activity.Summary, Activity.DateCompleted FROM Contact LEFT JOIN Activity ON Contact.ContactPK = Activity.ContactPK LEFT JOIN ActivityType ON Activity.ActivityTypePK = ActivityType.ActivityTypePK" , map : ( c , a ) => { Contact contactEntry ; if ( ! contactDictionary . TryGetValue ( c . ContactId , out contactEntry ) ) { contactEntry = c ; contactEntry . Activities = new List < Activity > ( ) ; contactDictionary . Add ( contactEntry . ContactId , contactEntry ) ; } contactEntry . Activities . Add ( a ) ; return contactEntry ; } , splitOn : "Type" ) . Distinct ( ) . ToList ( ) ; } return Ok ( contacts ) ; }

We can see in Postman that the endpoint responded much quicker:

We can also see in SQL profiler that only a single database call was made:

Multiple Result Sets

So, Dapper helps us reduce round trips when a controller action method is fetching related data with its “multi-mapping” feature. But what about cases when the API needs to return data from unrelated tables? How can we fetch unrelated data in a single database call?

As an example, let’s look at the implementing a single controller action for some contact “lookup” values that are needed for a screen in an app that is used by our API. Here are the database tables:

… and here’s an example of the response body we expect:

{ "addressTypes" : [ "Home" , "Work" ] , "emailAddressTypes" : [ "Home" , "Work" ] , "phoneNumberTypes" : [ "Home Landline" , "Work Landline" , "Mobile" ] }

i.e. We have 3 unrelated tables that we need to query data for and we’d like to do this in a single database hit. The good news is that we can use dapper’s multi-recordset feature to do this:

[ HttpGet ( "lookups" ) ] public IActionResult GetLookups ( ) { var lookups = new Lookups ( ) ; using ( SqlConnection connection = new SqlConnection ( _connectionString ) ) { connection . Open ( ) ; using ( GridReader results = connection . QueryMultiple ( @" SELECT Type FROM AddressType SELECT Type FROM EmailAddressType SELECT Type FROM PhoneNumberType" ) ) { lookups . AddressTypes = results . Read < string > ( ) . ToList ( ) ; lookups . EmailAddressTypes = results . Read < string > ( ) . ToList ( ) ; lookups . PhoneNumberTypes = results . Read < string > ( ) . ToList ( ) ; } } return Ok ( lookups ) ; }

If we hit this endpoint, it’s nice and quick:

Let’s check in SQL Profiler that this does only hit the database once:

Nice!

Comments

Sanjeev Agarwal February 26, 2018

Use sp 🙂

kenji March 3, 2018

omg! i really need to tuning web api service in my job, i will try both approach!!!