Scalable and Performant ASP.NET Core Web APIs: Database Round Trips

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.

Database model

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:

N+1 Problem 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!

SQL Trace of 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:

Fast contacts and activities

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

Fast contacts and activities SQL Trace

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:

Database Lookup model

… 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: ulti-Recordset Result

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

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!!!