Increasing Dapper’s Performance

Many times we start writing code and overlook details that can affect the performance of our system. We believe that the performance issue is bigger than it really is and, consequently, we end up taking more complicated paths.

Let’s consider something simple. You are using Dapper, a Micro ORM, which is known for its speed compared to other ORMs. But are you using Dapper correctly and taking full advantage of its performance?

Do you usually carefully identify the parameters you pass to your queries?

Let’s create a Client class and a table to store clients:

class Cliente
{
    public string Nome { get; set; }
    public int Id { get; set; }
    public string CPF { get; set; }
}

CREATE TABLE CLIENTE (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    NOME VARCHAR(200) NOT NULL,
    CPF CHAR(11) NOT NULL
)

After creating the table, let’s insert 1000 (fake) CPFs in a loop and create an index for CPF:

CREATE UNIQUE NONCLUSTERED INDEX IX_CLIENTE_CPF   
    ON Cliente (Cpf)
    INCLUDE (Nome)

Now, let’s write simple code to get a client through the CPF, which is common in many applications:

IConfigurationBuilder builder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", false, true);
        IConfigurationRoot config = builder.Build();

        SqlConnection sqlConnection =
                new SqlConnection(
                    config.GetConnectionString("ecommerceConnectionString")
                    );
        sqlConnection.Open();
        var cliente = sqlConnection.QueryFirstOrDefault<Cliente>(
            "select * from Cliente where CPF = @cpf",
            new { cpf = "00000000090" });
        sqlConnection.Close()

What we expect is that the query executed in the database is as performant as possible since we have an index by the CPF column. Let’s prove that the index exists and that we have the best execution plan possible.

The query was executed performing an Index Seek on the index by CPF that we created earlier.

Now let’s execute the “same” query through the application.

Executing the query directly in SQL and obtaining the execution plan.

The result was not as expected. Instead of performing an Index Seek, the query generated by Dapper performed an Index Scan. This happened because Dapper interpreted the query parameter as NVARCHAR by default, which caused SQL Server to convert the parameter and change the execution plan, affecting the query performance.

To avoid this issue and improve query performance, it is necessary to correctly inform the string parameter type. In Dapper, we can use the DbString class to specify the parameter type.

var cliente = sqlConnection.QueryFirstOrDefault<Cliente>(
            "select * from Cliente where CPF = @cpf",
            new
            {
                cpf = new DbString
                {
                    IsAnsi = true,
                    IsFixedLength = true,
                    Length = 11,
                    Value = "00000000090"
                }
            });

By setting the DbString properties, Dapper generates a query with the correct parameter type, allowing SQL Server to perform an Index Seek. Note that it is important to specify the properties correctly, according to the column data type in the database.

DbString is a class in Dapper that allows you to specify the exact type of a parameter that will be passed to the database. This way, Dapper generates a statement that uses the correct data type for the parameter. This avoids unnecessary conversions and helps improve query performance.

The DbString constructor allows you to set various properties, such as IsAnsi, IsFixedLength, Length, and Value. The IsAnsi property indicates whether the data type is ANSI or Unicode. The IsFixedLength property indicates whether the data type is fixed or variable length. The Length property indicates the maximum size of the field, and the Value property is the parameter value.

Parameters according to type:

This small detail often goes unnoticed during our implementations and can significantly affect the performance of our applications, directly impacting user experience. Therefore, it is important to take time to optimize the code, using best practices and tools that help us identify and fix potential problems.