Bad Dapper performance for parametrized queries

Question

I was researching porting some of our EF6 code to Dapper for better performance when I ran into a weird issue. A single row query was taking almost 10 times as much in Dapper than it did 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 about 80 columns, and the EF version uses the same exact query and the same model. For reference, this is the EF version:

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

I took into account that the first query might be slow, so I took measurements after a "warm up" period. I thought it might be an issue with reusing the EF model, so I created a simple POCO as a model. None of that worked. So I played around with it, trying different things, and decided to try to use a SQL-injectiony 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 query was actually faster than the EF one.

So what's happening here? Why is the parametrized query so much slower?

Answer 1

Based on your final example, it seems most likely that your column is varchar but when you use the parameterized query the parameter is being sent in as nvarchar. Since nvarchar to varchar could involve data loss, SQL converts each value in the table to nvarchar for comparison. As you can imagine, converting every row for comparison is slow and prevents the use of the index.

To work around this, you have two choices:

If your database doesn't use nvarchar at all, you can simply change the mapping during application startup:

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

Otherwise you can change it per query:

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

Answer 2

Its to do with the datatype of the paramater. If it doesn't match that of the index then it casts every row to compare it. Doing it as a string the type gets chosen by the sql parser.