Poor Dapper performance for parameterized queries

Question

I studied porting some of our EF6 code to Dapper to improve performance when I had a strange problem. A single line request took almost 10 times as much in Dapper as in EF. It looked like this:

using (IDbConnection conn = new SqlConnection("connection string")) { row = conn.Query<ReportView>("select * from ReportView where ID = @ID", new {ID = id})) .FirstOrDefault(); } 

This query targets a view with approximately 80 columns, and the EF version uses the same exact query and the same model. For reference, this is the version of EF:

 row = context.ReportViews.Where(s => s.ID == id).FirstOrDefault(); 

I took into account that the first request may be slow, so I took measurements after a period of “warm-up”. I thought this might be a problem of reusing the EF model, so I created a simple POCO as a model. None of this worked. So I played with it, tried different things and decided to try using the SQL injection of the concatenated SQL statement.

 using (IDbConnection conn = new SqlConnection("connection string")) { row = conn.Query<ReportView>(string.Format("select * from ReportView where ID = '{0}'", id)).FirstOrDefault(); } 

This request was faster than EF.

So what is going on here? Why is the parameterization request much slower?

Answer 1

Its relation to the paramater data type. If it does not match the index index, it throws each row for comparison. Performing this as a string, the type is selected by the sql parser.

Answer 2

According to your final example, it seems your column is varchar , but when using a parameterized query, the parameter is sent as nvarchar . Since nvarchar for varchar may include data loss, SQL converts each value to a table in nvarchar for comparison. As you can imagine, converting each row for comparison is slow and prevents the use of an index.

To get around this, you have two options:

If your database does not use nvarchar at all, you can simply change the display at application startup time:

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

Otherwise, you can change it for each request:

 row = conn.Query<ReportView>("select * from ReportView where ID = @ID", new {ID = new DbString { Value = id, IsAnsi = true }}) .FirstOrDefault();