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 allow the consumers of our API to request large collections of data by “paging” the data.

How paging helps performance

Paging helps performance and scalability in a number of ways:

• The number of page read I/Os is reduced when SQL Server grabs the data
• The amount of data transferred from the database server to the web server is reduced
• The amount of memory used to store the data on the web server in our object model is reduced
• The amount of data transferred from the web server to the client is reduced

This all adds up to potentially a significant positive impact - particularly for large collections of data.

Benchmark

We’re continuing to use Dapper for our data access. Let’s start with a simple controller action method for

[HttpGet]
public IActionResult GetContacts()
{
    IEnumerable<Contact> contacts = null;

    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        contacts = connection.Query<Contact>(@"SELECT ContactId, Title, FirstName, Surname
                                               FROM Contact");
    }

    return Ok(contacts);
}

This is what we get if we profile the SQL:

Let’s load test the API as well:

#asp.net #.net core #api #paging #sqlserver