SQL Server Hidden “Load Evil” (Performance Issue)With Dapper

Dapper is one of the best state of the art ORM out there for .NET developers and most of us are already using it in our application development. With Dapper introduced in to the application, we will definitely be expecting a good improvement in the performance of the entire application.

Unfortunately, most of you reading this article might have ended up here because you were not getting the expected result, but a performance drop instead. Perhaps you may be wondering what has gone wrong with your application or environment which is yelling at you with a spike in CPU usage and load on the server. This article may help you find the reason (if you have not figured it out already and hopefully this is the reason why you are facing the issue).

I’m sure you have read through the Dapper documentation and dapper tutorial, but may have missed the importance of this part in your environment. It clearly instructs to use DbString when you are passing a string parameter to Dapper for building your query.

For a better understanding, let us take a look at what Dapper does with it’s parameters. When a string is passed as a parameter, Dapper assumes the type of it as NVARCHAR even if the column in your DB is VARCHAR. Ta-da !!! Now, if you have used these parameters in your WHERE clauses, your DB have to compare a VARCHAR column with an NVARCHAR data. Seeing this, the DB will simply decide that it cannot use the index on the table. That means, instead of using Seek on the table, it will do a Scan ! Also it has to convert each row of the table to NVARCHAR for comparing.

(Just a Pro tip: NVARCHAR and NCHAR supports UNICODE characters where as VARCHAR and CHAR supports only ANSII characters)

Just imagine how much load it creates on the server. If you can’t (or don’t want to) imagine, let us try to quantify the above observation using a simple-sample Dapper implementation.

Simple-Sample

Let us create an Employee class as follows:

public class Employee
{    
    public string empID { get; set; }
    public string empFirstName { get; set; }
    public string empLastName { get; set; }
}

And we shall also create a table for the data as follows:

CREATE TABLE  (
    empID VARCHAR(15) PRIMARY KEY,
    empFirstName VARCHAR(255) ,
    empLastName VARCHAR(255) 
);

And let us use Dapper to read and map the active record to an instance of Employee. (For those of you wondering why the id is a string instead of number, I just wanted to demonstrate what happens with string in Dapper).

static void Main(string[] args)
{
    DbConnection db = new SqlConnection(
                         ConfigurationManager
                           .ConnectionStrings["DapperSQLConString"]
                           .ConnectionString
                      );
    db.Open();
    employee = db.QueryFirst<Employee>(
        "select * from employeeDetails 
         With (nolock) where empID = @empID ", 
        new { @empID = "1" });
}

Now if we run this console application with SQL Profiler on in Microsoft SSMS, we can find the query that got executed for the above DB hit. It will be something like this:

EXEC sp_executesql N'Select * from employeeDetails WITH(NOLOCK) where empID=@empID' ,N'@empID nvarchar(4000)' ,@empID = N'1'

Did you see that? Query has everything in NVARCHAR!!!

We were expecting something like

EXEC sp_executesql N'Select * from employeeDetails WITH(NOLOCK) where empID=@empID' ,N'@empID VARCHAR(15)' ,@empID = '1'

but we got every thing in NVARCHAR(4000) instead of VARCHAR(15).

If you are still wondering what difference it would make on performance, I recommend you to run the above queries in SSMS and look at the execution plan generated. You will see it for yourself.

The SQL server has to convert every row to NVARCHAR and then compare, which is not at all desirable with larger tables. Instead of using index seek, it now has to scan the table to fetch the result. Here is an example of cost comparison of index seek vs scan on a table (a different table than discussed here, but with same problem in Dapper query) with large number of rows.

Comparison of costs generated from execution plan for Index Scan VS Index Seek

If we look at the number of rows read, estimated CPU cost, operator cost, I/O cost and subtree cst, we can realize how heavy this is gonna be on the server.

So how do we solve this?

We have two ways to resolve this. One way is, you can tell Dapper that every argument that comes as a string should be treated as VARCHAR.

Dapper.SqlMapper.AddTypeMap(typeof(string),System.Data.DbType.AnsiString);

But this is good only when you don’t use NVARCHAR columns in your entire database. Better way which I would recommend you is to specify it at the consumption level using DbString. So the above code becomes:

employee = db.QueryFirst<Employee>(
        "select * from employeeDetails 
         With (nolock) where empID = @empID ", 
        new { @empID = new DbString { Value = "1", IsFixedLength = false, IsAnsi = true, Length = 15 } });

Well, now we will get the exact same query we expected Dapper to generate. This is the exact reason why DbString exists!!!

I hope we are clear here and this might solve an issue we faced at some point while using Dapper. At the time of this article this has been verified with version 1.60.6 and obtained the details specified here.

For those who need a quick and concise reference on how DbString decides the type, check this out:

Hope this helped. Happy coding!