Performance tuning - C# Dapper and SQL char

In this post I will explain how a simple SQL query executed with Dapper can lead to performance issues and how I managed to fix it - by improving significantly the execution time.

Let's consider that I have table called Sales with at least 100 millions records. The requirement is to retrieve all the sales for a specific Company, Region and Location combination. Company, Region and Location are fixed sized and stored as char to improve the performance.

CREATE TABLE Sales (
  ID int unique,
  COMPANY CHAR(5),
  REGION CHAR(2),
  LOCATION CHAR(5)
  PRICE DECIMAL(18,2)
)

The requirement can be solved with a very simple query. If the query is run in SQL management, the execution time is less than a second (good performance).

DECLARE @Location char(5) = '334Af'
DECLARE @Company char(5) = 'A2312'
DECLARE @Region char(2) = 'EU'

SELECT * 
FROM Sales
WHERE COMPANY = @Company AND Region = @Region AND LOCATION = @Location 

Next step is to run the query in our C# application. This can be achieved in multiple ways but for this post I will use Dapper (version 1.50).

string query = @"
   SELECT * 
   FROM Sales 
   WHERE COMPANY = @Company AND Region = @Region AND LOCATION = @Location";

await sqlConnection.QueryAsync<dynamic>(query, new {
  Company = "334Af",
  Region = "A2312",
  Location = "EU"
});

Although the same query is passed to the Dapper method, the execution time increased up to twenty seconds (up to twenty times slower than SQL management). My first thought was that the Dapper library contains a performance issue. But after a deeper analysis I discovered that the my code was the problem. Behind the scene Dapper is executing every SQL query with sp_executesql.

EXECUTE sp_executesql N'SELECT * 
   FROM Sales 
   WHERE COMPANY = @Company AND Region = @Region AND LOCATION = @Location
', N'@Location varchar(4000), @Company varchar(4000), @Region varchar(4000)',
@Company = 'A2312', @Region = 'UE', @Location = '334Af'

By default Dapper send input string parameters as varchar parameters. So every time a column (char) is compared with the input parameter (varchar) a casting is needed. For huge tables this process can be very slow. A simple solution to avoid the casting is to send the parameters as char. This can be done by using DbString class (part of Dapper assembly).

await sqlConnection.QueryAsync<dynamic>(query, new {
  Location = new DbString { Value = location, IsFixedLength = true, Length = 5, IsAnsi = true }
  Company = new DbString { Value = company, IsFixedLength = true, Length = 5, IsAnsi = true },
  Region = new DbString { Value = region, IsFixedLength = true, Length = 2, IsAnsi = true }
});

This change made my application to run almost similar to SQL management (around one second).