This is another post in a series on creating performant and scalable web APIs using ASP.NET Core. In this post, we’ll continue to focus on operating on large collections of data - this time allowing the consumers of our API to filter and search on the collection …

How filtering and searching helps performance

The reasons are the same as paging from the last post:

• 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

It also means the builder of the client application that consumes our API doesn’t have to do any client side filtering or searching which can have a nice positive impact on the overall performance as well.

Filtering

Filtering reduces a collection via criteria on filterable fields in the resource. Let’s implement a controller action method for: GET api/contacts?firstname={firstname}&surname={surname}. We’ll continue to use Dapper as our data access library …

The code is below. In summary, we construct the SQL, including the appropriate WHERE clause if filter values have been supplied in the URL.

[HttpGet]
public IActionResult GetContacts(UrlQuery urlQuery)
{
    IEnumerable<Contact> contacts = null;
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        string sql = @"SELECT ContactId, Title, FirstName, Surname
                       FROM Contact";
        if (urlQuery.HaveFilter)
        {
            string filterSQL = "";
            if (!string.IsNullOrEmpty(urlQuery.FirstName))
            {
                filterSQL += " FirstName = @FirstName";
            }
            if (!string.IsNullOrEmpty(urlQuery.Surname))
            {
                if (!string.IsNullOrEmpty(filterSQL))
                {
                    filterSQL += " AND";
                }
                filterSQL += " Surname = @Surname";
            }
            sql += $" WHERE {filterSQL}";
        }

        contacts = connection.Query<Contact>(sql, urlQuery);
    }

    return Ok(contacts);
}

Below is the model class for the filtering information that binds to the URL parameters:

public class UrlQuery
{
    public string FirstName { get; set; }
    public string Surname { get; set; }

    public bool HaveFilter => !string.IsNullOrEmpty(FirstName) || !string.IsNullOrEmpty(Surname);
}

Looking at the profiled SQL for GET api/contacts?surname=Kanne, we see that it is nice and efficient:

Looking at the SQL execution plan, we see that it is using an index on Surname:

Let’s also load test this endpoint:

Pretty good!

Searching

Searching reduces a collection by allowing the API consumer to specify a single piece of criteria. For example: GET api/contacts?search=smith. It’s up to the API which fields in the resource are filtered on.

Let’s go ahead and implement searching on our contacts resource. The highlighted code makes use of a Full-Text index which is on FirstName and Surname.

[HttpGet]
public IActionResult GetContacts(string search)
{
    IEnumerable<Contact> contacts = null;
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        string sql = @"SELECT ContactId, Title, FirstName, Surname
                       FROM Contact";
        if (!string.IsNullOrEmpty(search))        {            sql += " WHERE CONTAINS(Contact.*, @Search)";        }
        contacts = connection.Query<Contact>(sql, new { Search = search });
    }

    return Ok(contacts);
}

Let’s have a look at the profiled SQL for GET api/contacts?search=carl, we see that it looks efficient:

Looking at the SQL execution plan, we see that it is using our Full-Text index, which is great:

Let’s also load test this endpoint:

Not bad!

Considerations

We should always consider creating indexes on filterable fields to ensure the SQL queries are nice and efficient. We’ll be getting slow full table scans if a field isn’t covered by an index.

We should always consider creating a full-text index when providing searching on our API resource collection. It’s much more efficient on large collections that doing something like WHERE FirstName LIKE '%' + @Search + '%' OR Surname LIKE '%' + @Search + '%' …

Conclusion

It’s a good idea to include filtering or searching (or both) on API GET resources. It not only makes the API really nice to consume when the client app needs to access a subset of the resource collection, but it provides a really efficient way of doing this.